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: 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: 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: 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: 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: 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: 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: 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: 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: 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]
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: 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]
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: 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: 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]
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
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: 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: 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: 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
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: 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: Query Help
SELECT id, count(*) AS cnt FROM `table_name` GROUP BY id ORDER BY cnt DESC [ LIMIT 1 ] -- Gabriel PREDA Senior Web Developer
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
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
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
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(overpar<0, @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: 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: On Duplicate Key....
Try: INSERT *IGNORE* INTO table (a,b) VALUES (1,2) -- 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: 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: 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: 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 : mysql>SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; For altering the sessions sql_mode... now you will have to insert the dump with: mysql>SOURCE /path/to/dump.sql You can change it globally mysql>SET @@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: 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: 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: 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: 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: 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
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
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: 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: 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: 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: fulltext searches
Hi Octavian,
Yes indeed... "IT" is a stop word... despite being the acronym for
"Information Technology"... *It* is a third-person neuter
pronoun<http://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: 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 > > > > >
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: 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]
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]
Re: mysql install on Redhat Linux 2.1AS (32 bit)
You should be shure that the old client is not in the PATH... because seems to me that when you're not int the mysql bin directory the old client is called... withc does not know about the new auth methods... Or else... start the server with the "--old-password" param. Gabriel - Original Message - From: "V. Agarwal" <[EMAIL PROTECTED]> Subject: mysql install on Redhat Linux 2.1AS (32 bit) > However, it expects me to be in mysql bin directory to > invoke 'mysql' to connect or else it gives me > following error. > > mysql -uroot -p > ERROR 1251: Client does not support authentication > protocol requested by server; consider upgrading MySQL > client. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird Query Result
Not weird at all...
As you can see it returnet all rows where "iImo" equals 0... that is becuase
of the CAST applied...
iImo is int(11) thus MySQL is casting 'FOOBAR' and the result is 0.
Try:
mysql> SELECT CAST('FOOBAR' AS UNSIGNED);
MySQL will yell:
+--+
| CAST('FOOBAR' AS UNSIGNED) |
+--+
|0 |
+--+
1 row in set (0.03 sec)
I hope this is clear !
Gabriel PREDA
www.amr.ro
www.lgassociations.info
dev.falr.ro
- Original Message -
From: "Mattias Håkansson" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, April 05, 2005 12:19 PM
Subject: Weird Query Result
Hello,
I'm using MySQL 4.0.20 on Linux and I am experiencing some problems with a
query result.
I have the following table structure:
mysql> desc gen_Lloyds_vessel;
+-+--+--+-+-+---
-+
| Field | Type | Null | Key | Default | Extra
|
+-+--+--+-+-+---
-+
| iLloydsvesselID | int(11) | | PRI | NULL|
auto_increment |
| cName | char(50) | | | |
|
| iImo| int(11) | | | 0 |
|
| cCountry| char(5) | | | |
|
| iYearbuilt | int(11) | | | 0 |
|
| cVesseltype | char(40) | | | |
|
| iEnteredby | int(11) | | | 0 |
|
| tEntered| datetime | | | -00-00 00:00:00 |
|
| iUpdatedby | int(11) | | | 0 |
|
| tUpdated| datetime | | | -00-00 00:00:00 |
|
+-+--+--+-+-+---
-+
10 rows in set (0.00 sec)
Look at the query below:
mysql> SELECT cName,iImo FROM gen_Lloyds_vessel WHERE gen_Lloyds_vessel.iImo
= 'FOOBAR';
+--+--+
| cName| iImo |
+--+--+
| SSG EDWARD A. CARTER |0 |
| LYKES HERO |0 |
|
PONL GENOA |0 |
| YM MILANO|0 |
| PONL NEWARK |0 |
| COSCO NORFOLK|0 |
| PONL JAKARTA |0 |
| PONL SYDNEY |0 |
| PONL GENOA |0 |
| PONL MARSEILLE |0 |
+--+--+
10 rows in set (0.07 sec)
None of these iImo fields has the value 'FOOBAR' but still I receive these
results.
Anyone have a clue what is causing this?
Thank you for your time,
Mattias Håkansson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does a multi-row INSERT work?
Ok. I believe you got your answer... for the syntax...
I just want to add that this is faster because... using this you only modify
the index file once.
Lets see for:
INSERT INTO x VALUES (a,b);
INSERT INTO x VALUES (c,d);
The server does:
open table
INSERT INTO x VALUES (a,b);
update index
close table
open table
INSERT INTO x VALUES (c,d);
update index
close table
But for
INSERT INTO x VALUES (a,b),(c,d);
The server does:
open table
INSERT INTO x VALUES (a,b),(c,d);
update index
close table
Now it's clear why multi-row INSERT is faster.
Of course THIS IS a faulty explanation ("grosso modo" in latin) but show
somehow what's going on !
Gabriel PREDA
- Original Message -
From: "Chris W. Parker" <[EMAIL PROTECTED]>
Subject: How does a multi-row INSERT work?
> Hello,
> I searched the archives, looked through the manual, and searched google
> for info on how to actually perform a multi-row INSERT but didn't find
> an answer.
> Would someone please show me the syntax for this please?
> I could just do a loop and INSERT the data that way but according to the
> manual, a multi-row INSERT is faster.
> Thanks,
> Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using one query to save data in 4 tables
Here... look at this: LOCK TABLES `presa_im` WRITE, `presa_im_titlu` WRITE, `presa_im_continut` WRITE; INSERT INTO `presa_im` (`nr`, `pag`, `ordine`) VALUES (5, 1, CEILING(RAND()*1000)); SELECT @ID:=LAST_INSERT_ID(); INSERT INTO `presa_im_title` VALUES(@ID, 'TITLE in Romanian', 'TITLE in English', 'TITLE in French'); INSERT INTO `presa_im_content` VALUES(@ID, 'Content in Romanian', 'Content in English', 'Content in Fench'); UNLOCK TABLES; This way you do not need to actualy capture the value of the last insert id from MySQL in your application... Gabriel PREDA www.amr.ro www.lgassociations.info - Original Message - From: "James Black" <[EMAIL PROTECTED]> To: Sent: Saturday, March 26, 2005 12:14 AM Subject: re: using one query to save data in 4 tables > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I am curious if this would be possible, and which version of mysql would > be needed. > > Basically, I want to store a user in one table, then get the id for the > user I just saved, and store three more rows, each in a different > database, using the user's id in the insert statements. > > Thanx. > > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black[EMAIL PROTECTED] > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.5 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFCRH8iikQgpVn8xrARAsw5AJ0SxPoMMcy1QUa1GNNEdfg51Q7Q8ACfYHg7 > NdFWteuQU4JjSfx7yYS++9k= > =JZD5 > -END PGP SIGNATURE- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with default-collation
H... It looks like you upgraded from a MySQL that doesn't know about collations... All the tables created with a version of MySQL that doesn't know about collation are considered by a version that does know... being of collation "latin1_swedish_ci"... (on columns where collation makes sense... I hope this is implied...) If you created the tables under 4.1.8... then ... then there is a problem... are you sure MySQL is reading your CNF ? Is there another CNF that overrides the one that has the desired settings ? If not... then you will need to ALTER the table(s)... but be carefull... ALTER them in 2 steps... ALTER all the columns in the table to a BINARY data type... then ALTER the table's collation to the desired one... finaly ALTER all columns back but now using the desired collation !!! Hope this helps ! Gabriel PREDA www.amr.ro www.lgassociations.info dev.falr.ro - Original Message - > Hi, > I am using mysql Ver 14.7 Distrib 4.1.8, for pc-linux (i686) > When running a certain query I get the following error: > DBD::mysql: t execute failed: Illegal mix of collations > (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation > 'find_in_set' > I do not understand why I have a mix of collations since in the my.cnf I > indicate: > [mysqld] > default-character-set = utf8 > default-collation = utf8_general_ci > Where is the latin1_swedish_ci coming from? why isn't default-collation > overwriting it? > Appreciate any help. > Thanks > Dana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
> It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables,
Hi, Carlos > - Where can i get (url of document) the MySql database fields ? (For example, i > need to list all tables of a database (database: mydb) and for each table > properties like comments, size, record number, etc ?). You should use "SHOW TABLE STATUS" it will provide you with any info that you need. > - I need to do the same with all fields of each table. (The output i need is > explain above) > Fields: field(0), field(1), field(2), etc.. > Records: 1,MyClient_1,56888554, etc... You can use any of the: DESCRIBE `table_name` SHOW COLUMNS FROM `table_name` But the most verbose output you will get using: SHOW FULL COLUMNS FROM `table_name` This will show you collation, privileges the user has for the each column and per-column-comment. Verbose descriptions of the syntax can be found at http://dev.mysql.com/doc/mysql/en/show.html Good luck. Gabriel PREDA dev.falr.ro www.amr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FullText StopWordFile
How internationalized is the ft_stopword_file ? How can I create a custom ft_stopword_file ? Is the build in one for english only ? Gabriel PREDA www.amr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compare dates
Reinhart's request was to keep it's DATE type in place instead of changing it to DATETIME... that's why I emphasized that DATE is better and he should keep it that way ! (Comming back to it if I think... a DATE column beside a TIME column would use 6 bytes... not 8 bytes as DATETIME... at 100 million rows that would make a difference at about 190MB... Or is it "Premature Optimization" ??) For the optimization issue you're right... Regarding TIMESTAMP... Reinhart didn't told us his MySQL version... as of 4.1.2 ... you have better control over TIMESTAMP columns ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compare dates
Let me assure you that DATETIME is the worst choice ever... because it need 8 bytes per record... TIMESTAMP uses only 4 DATE uses only 3, so does TIME YEAR is the smallest... 1 byte. You can do: SELECT * FROM activities WHERE act_date >= NOW() But for optimization... you should not compute in WHERE... so this is faster.. .but there are 2 queries: SET @this_moment = NOW(); SELECT * FROM activities WHERE act_date >= @this_moment Gabriel PREDA www.amr.ro - Original Message - From: Reinhart Viane To: mysql@lists.mysql.com Sent: Friday, February 18, 2005 1:10 PM Subject: compare dates Hey list I need a query like this: Select * from activities where act_date = today or any day in the future I have made it work like this: Sselect * from activities where UNIX_TIMESTAMP()< UNIX_TIMESTAMP(act_date) Problem is with this thing I have to manually add 23:59:59 to each activity date I enter in the database. I'm sure there is a better way so I can set my column type to DATE instead of DATETIME now and use a better query. Any help? Thx Reinhart
Re: EXPLAIN: Select tables optimized away
O I see... I read this in MySQL Manual... I believe it's something like SHOW TABLE STATUS LIKE 'table_name' And then extract the number of rows... I never saw "Select tables optimized away" and it confused me ! Gabriel PREDA - Original Message - From: "O'K Web Design" <[EMAIL PROTECTED]> Subject: Re: EXPLAIN: Select tables optimized away > Hi > > Counts are extremely fast and since you have no WHERE statement, it > takes the count value straight from the internals and does not look at the > tables or an index if I remember correctly. Mike > > > - Original Message - > From: "Gabriel PREDA" <[EMAIL PROTECTED]> > To: > Sent: February 17, 2005 6:15 AM > Subject: EXPLAIN: Select tables optimized away > > > > MySQL 4.1.10 > > > > What does "Select tables optimized away" mean ? > > > > mysql> explain SELECT COUNT(*) AS total FROM members_twining_main; > > | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL > | > > NULL | Select tables optimized away | > > > > > > Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXPLAIN: Select tables optimized away
MySQL 4.1.10 What does "Select tables optimized away" mean ? mysql> explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: instable behaviour of mysql
First of all you have an eroneus update statement: UPDATE SET activ = 'inactiv' WHERE ident = 'fai' After UPDATE the tablename must be pesent UPDATE `tble_name` SET activ = 'inactiv' WHERE ident = 'fai' Now next in line... REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' You are revoking user 'fai' from host 'mydom.tld' at database 'fai_accounts'... but this has nothing to do with show grants for current_user() Because that will show the grant 'source' for the current user that I think is not 'fai' but 'root' as you output says... To see grants for 'fai'@'mydom.tld' you should use: SHOW GRANTS FOR 'fai'@'mydom.tld' Hope it helps ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld error
> I am trying to get mysql working on my Windows ME machine. When trying > Server\bin\mysqld unknown option '--enable-named-pipe' > MySQL version 4.1.9. > Any help greatly appreciated. > Dick WinME does not support named pipes... so neither mysqld... so you should remove from my.cnf or my.ini witchever you use... any declaration on named pipes... Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between 'LIKE' and '='
> | But I have a question: is there any difference between the following? > | SELECT lname, fname FROM contacts WHERE lname = 'smith'; > | SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; > | Sincerely, > | -Josh > My > gut hunch is that if your LIKE expression is going to contain no wildcards, > you should probably write it as an '=' simply because it is likely to > perform better. > If anyone reading this is knowledgeable on MySQL performance, please jump in > and correct me if I'm wrong. > Rhino There is nothing wrong... If you'll use '=' you will get in EXPLAIN SELECT a type of 'ref' and if you're using 'LIKE' you will get a type of 'range' witch is slower than 'ref'.. I have a table that stores id of a city, ccode is the country code for that city, and city... is a varchar containing the name of the town... there is an index '2din3' on 'ccode,city' let's see: Queries: EXPLAIN SELECT * FROM `com_cities` WHERE `ccode` LIKE 'EN'; EXPLAIN SELECT * FROM `com_cities` WHERE `ccode` = 'EN'; Results showing only differencies: type; ref range;NULL ref;const Of course things for me would make no big difference since `ccode` si a 2 letter CHAR... but for a varchar and a big table would ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and Transactions
No the transaction will not be rolled back... BUT (always the BUT thing)... If you're not carefull to open a NEW link in MySQL setting the fourth parameter to "mysql_connect" to TRUE... PHP will not open a new link but return the already created one... thus entering in the same transaction... that is IT WILL BE ROLLED BACK ! Good fortune ! Gabriel PREDA > -Original Message- > Andre Matos @ February 07, 2005 6:45 PM > Let's suppose that I have this sequence of events: > - create a connection "1" > -- start a transaction > --- create a new connection "2" > insert a new record "named B" > --- close the connection "2" > --- insert a new record "named A" > -- rollback > - close the connection "1" > This sequence will be written in PHP4. My question is: > Will the record "named B" be written in the database or it will be rollback > with the record "named A"? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
I didn't found an IF ... ELSE ... in MySQL ... and I'm using it for at least 4 years !... since the old 3.23.xx times !!! So you will have to do: SELECT a,b,c, IF(Location=1, 'Downstairs Cat Room', IF(Location=2, 'Kitten Room', IF(Location=3, 'Quarantine', 'Unknown') ) ) as Location FROM `table_name` Syntax for IF is: IF(expr1, expr2, expr3)... that means IF expr1 == TRUE THEN expr2 ELSE expr3 As you saw in the response I gave you I used instead of expr3 another IF... and so on !!! Gabriel PREDA www.amr.ro www.lgassociations.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE INTO //add or update?
It simple... if mysql_affected_rows == 2 ---> update (because replace make DELETE and INSERT) else if mysql_affected_rows == 1 > insert Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what happen if exceed max connection in innodb
Hi Chenri, > 1. is this value for one database or for overall mysql connections? The value is for overall mysql connections... so it's not for one database. > 2. i'm unable to generate 100 connections, i don't have 100 workstations yet, > how do i test it? Why would you ? > 3. what will happen if the connection exceed 100 connection? > (does mysql crash or display database unavailable error message > or it just queued the connection requests) No it does not crash... It will refuse connection number 101... but there is an issue... depending on the OS there is a "number of connections" that the OS can keep in state of pending... until the application can accept it... so you can get a faked number... Let's say you work with PHP... a PHP script takes under a second to execute then all connections are closed... so it is possible that the OS can keep the connection number 101 in state of pending untill there are only 99 active connections on MySQL... yours becoming connection nr 100 ... MySQL will accept it and serve it... > 4. how should i measure the cache and ram needed for the value of connections? Run it with "acceptable parameters" ... if you see in "SHOW STATUS" high values for "Aborted_connects" or "Aborted_clients" then you should worry... Also you might wanna take a look @ Jeremy's "mytop" (http://jeremy.zawodny.com/mysql/mytop/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Syntax diagram, where is it located in the doc?
You're right there is none.. .but as you see there is a short line that says: "In the WHERE clause, you can use any of the functions that MySQL supports, except for aggregate (summary) functions. See section Functions and Operators." Gabriel PREDA > - Original Message - > From: "Thomas Sundberg" <[EMAIL PROTECTED]> > To: > Sent: Friday, February 04, 2005 2:46 PM > Subject: RE: Syntax diagram, where is it located in the doc? > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: den 4 februari 2005 12:45 > > > To: [EMAIL PROTECTED] > > > Cc: mysql@lists.mysql.com > > > Subject: Re: Syntax diagram, where is it located in the doc? > > > > > > "Thomas Sundberg" <[EMAIL PROTECTED]> wrote > > > on 04/02/2005 > > > 11:39:12: > > > > > > > Hi! > > > > > > > > I'm looking for the syntax diagram for MySQL and can't find > > > it. I have > > > > downloaded the entire MySQL manual as one html page and > > > searched it > > > > for > > > the > > > > definition of where_definition and I cant find it. Could somebody > > > > please point me to a location where the complete syntax > > > diagram can be found? > > > > > > > > Does anybody at the list know the answer to my question? I > > > sent it a > > > > few days ago and haven't received any response. It does > > > exist a syntax > > > diagram > > > > for MySQL, doesn't it? > > > > > > Since no-one replied to your first post, apparently not. I > > > have never seen such a thing. > > > > Strange, where is the definition for the syntax element "where_definition" > > done then? That is the part of the syntax diagram I currently looking for. > > It is defined as an element in the select syntax diagram. But when trying > to > > find the definition for what is legal to put in a where clause, I just > can't > > find it. > > > > Could somebody point in me the correct direction? > > > > /Thomas > > > > > > -- > > 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: SHOW TABLES Problem
Because it does not know REGEXP... only LIKE patterns... % Matches any number of characters, even zero characters _ Matches exactly one character Taake a look in chapter 13 "String Comparison Functions". Gabriel PREDA - Original Message - From: "shaun thornburgh" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 02, 2005 1:03 PM Subject: SHOW TABLES Problem > Hi, > > The syntax for show tables from the manual is: > > SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern'] > > so whay doesnt the following query work: > > SHOW TABLES LIKE REGEXP 'PID_[0-9]+'; > > Thanks for your help > > Shaun > > > > -- > 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: Best way to store numeric data?
You could use DECIMAL(17,15) - but it will take you 17+2bytes per record... but you could split the number in 2 parts before the DOT . because you have 2 number use TINYINT- takes 1 byte BIGINT - takes 8 bytes So you have a total of 9 bytes DOUBLE - and it will take you 8 bytes per record So DOUBLE it's a good choice... Of course depending on what you do with the numbers the separation could provve better even if you loose one byte pe record ! Gabriel PREDA - Original Message - From: "Galen" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 02, 2005 9:15 AM Subject: Best way to store numeric data? > I've got a huge table going, and it's storing a load of numeric data. > Basically, a percentage or single digit rank, one or two digits before > the decimal and fifteen after, like this: > > 6.984789027653891 > 39.484789039053891 > > What is the most efficient way to store these values? I will be > frequently sorting results by them or using math with them, so speed is > important, but I also don't want to be wasteful of disk space as I > currently have over three quarters of a million records, with more to > come. > > > -- > 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: utf8 x latin
I think you should use: latin1_general_ci and on columns with spanish you should use latin1_spanish_ci. English, French, Portuguese not having a separate collation I believe that they are included into latin1_general_ci ? Please observe the last part from a collation name: case sensitive (_cs) ... case insensitive (_ci) ... and binary (_bin) ! UTF8 consumes more space on disk ! If you issue a SHOW CHARACTER SET command mysql> SHOW CHARACTER SET; You will notice the last column named Maxlen that defines how many bytes takes to store a letter. On utf8_general_ci you will notice 3... as on latin1__xx you will only see 1... Although these values represent a MAX value... what you need will use at most 2 bytes per letter in UTF-8... but in latin1_xxx_xx will, for sure, will use one byte... on long texts this will count ! In my opinion you should stick to latin1_xxx_xx ! Afther some thought and aditional computation I declared myself satisfied with: latin2 - ISO 8859-2 Central European - latin2_general_ci - 1byte but I'm having Romanian + English + French columns. Try to read http://dev.mysql.com/doc/mysql/en/charset.html with no distraction arround you ! Gabriel - Original Message - From: "Andre Matos" <[EMAIL PROTECTED]> To: Sent: Sunday, January 30, 2005 9:29 PM Subject: utf8 x latin > Hi list, > I was wondering which one is better to use, "utf8_general_ci" or > "latin1_general_cs", with English, French, Portuguese, and Spanish? > Thanks. > Andre > -- > Andre Matos > [EMAIL PROTECTED] > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
