Re: convertion to utf-8
2008/7/1 Dan Nelson [EMAIL PROTECTED]:
In the last episode (Jun 30), Pooly said:
2008/6/30 Dan Nelson [EMAIL PROTECTED]:
In the last episode (Jun 29), Pooly said:
Hi,
I'm trying to convert my tables to UTF8 but I'm getting the
following error: ERROR 1062 (23000): Duplicate entry 'Zorglüb' for
key 1
Not too sure why I'm getting this error since the current (latin1)
data are:
mysql select * from topics_lookup where label like 'Zor%';
+--+--+--+
| label| topic_id | main |
+--+--+--+
| Zorglub | 72 |0 |
| Zorglüb | 72 |1 |
+--+--+--+
2 rows in set (0.00 sec)
There is a unique index on label, however the 2 data are different.
Any ideas ?
I can't reproduce this. Can you provide example commands
demonstrating your problem?
Yes, sorry I should have been more precise in my email.
mysql select version();
+--+
| version()|
+--+
| 5.0.32-Debian_7etch5-log |
+--+
1 row in set (0.00 sec)
create table mytable2 ( label varchar(200) primary key ) charset latin1;
insert into mytable2 values ('Zorglub'), ('Zorglüb');
alter table mytable2 convert to character set utf8 collate utf8_general_ci;
this gives:
ERROR 1062 (23000): Duplicate entry 'Zorglüb' for key 1
I tried to search the changelog and the bug tracking system, but
without much luck.
Mysql's default collation is latin1_swedish_ci, which sorts ü along
with y. utf8_general_ci sorts it along with u:
http://www.collation-charts.org/mysql60/mysql604.latin1_swedish_ci.html
http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html
More reading:
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html
... To further illustrate, the following equalities hold in both
utf8_general_ci and utf8_unicode_ci (for the effect this has in
comparisons or when doing searches, see Section 9.1.5.6, Examples of
the Effect of Collation):
Ä = A
Ö = O
Ü = U
Thanks for the link and the detailled explanation. It's all clear now
with the collation, and I now what to do with my data.
Cheers,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convertion to utf-8
Hi,
2008/6/30 Dan Nelson [EMAIL PROTECTED]:
In the last episode (Jun 29), Pooly said:
Hi,
I'm trying to convert my tables to UTF8 but I'm getting the following error:
ERROR 1062 (23000): Duplicate entry 'Zorglüb' for key 1
Not too sure why I'm getting this error since the current (latin1) data are:
mysql select * from topics_lookup where label like 'Zor%';
+--+--+--+
| label| topic_id | main |
+--+--+--+
| Zorglub | 72 |0 |
| Zorglüb | 72 |1 |
+--+--+--+
2 rows in set (0.00 sec)
There is a unique index on label, however the 2 data are different.
Any ideas ?
I can't reproduce this. Can you provide example commands demonstrating
your problem?
Yes, sorry I should have been more precise in my email.
mysql select version();
+--+
| version()|
+--+
| 5.0.32-Debian_7etch5-log |
+--+
1 row in set (0.00 sec)
create table mytable2 ( label varchar(200) primary key ) charset latin1;
insert into mytable2 values ('Zorglub'), ('Zorglüb');
alter table mytable2 convert to character set utf8 collate utf8_general_ci;
this gives:
ERROR 1062 (23000): Duplicate entry 'Zorglüb' for key 1
I tried to search the changelog and the bug tracking system, but
without much luck.
Rgds,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
convertion to utf-8
Hi, I'm trying to convert my tables to UTF8 but I'm getting the following error: ERROR 1062 (23000): Duplicate entry 'Zorglüb' for key 1 Not too sure why I'm getting this error since the current (latin1) data are: mysql select * from topics_lookup where label like 'Zor%'; +--+--+--+ | label| topic_id | main | +--+--+--+ | Zorglub | 72 |0 | | Zorglüb | 72 |1 | +--+--+--+ 2 rows in set (0.00 sec) There is a unique index on label, however the 2 data are different. Any ideas ? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning about truncated data
Hi, I'm reloading a backup and I'm getting loads of warnings like : +-+--++ | Level | Code | Message| +-+--++ | Warning | 1265 | Data truncated for column 'page_public' at row 1 | +-+--++ I'm using 5.0.32-Debian_7etch1-log, according to these entry it has been fixed : http://bugs.mysql.com/bug.php?id=25815 But it's really unclear wheter it really truncate data, or if it's just a spurious warning. anyone had the same issue ? Thanks, -- http://www.myspace.com/sakuradropsuk : credit runs faster http://www.w-fenec.org/ Rock Webzine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_upgrade script problems on MySQL 5.0.24
Hi, could it be this bug : http://bugs.mysql.com/bug.php?id=21011 2006/8/31, Whisler, David [EMAIL PROTECTED]: I've upgraded from 5.0.22 to 5.0.24 MySQL Server on Solaris 9 using the Solaris package utility (which means I de-installed 5.0.22 then installed 5.0.24 in the same location, environment, configuration). I'm having the following error when trying to execute the mysql_upgrade executable according to the directions. This may not be necessary given it's a minor upgrade, but still - it should work, right? Here's the error. -- $ mysql_upgrade -u=root -p --basedir=/u01/app/mysql --datadir=/u07/mysql/data -v Enter password: Running /u01/app/mysql/bin/mysqlcheck --defaults-extra-file=/u07/mysql/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user==root error: Found option without preceding group in config file: /u07/mysql/data/upgrade_defaults at line: 2 Fatal error in defaults handling. Program aborted Error executing '/u01/app/mysql/bin/mysqlcheck --defaults-extra-file=/u07/mysql/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user==root' -- What's the deal with the upgrade_defaults file that it's complaining about? This file doesn't exist anywhere on my server (does it create it automatically), and according to the instructions it should be using the options I setup on the command line (and if not, it should at least be using the my.cnf file that I've setup in the MYSQL base directory. Here's my mysql.cnf (Note, I added the [mysql_upgrade] group options to see if that made a difference - and it didn't). $ more my.cnf [mysqld] basedir=/u01/app/mysql datadir=/u07/mysql/data #log #log-bin log-slow-queries [mysql_upgrade] basedir=/u01/app/mysql datadir=/u07/mysql/data --- This is on my test box (I'm no dummy - or at least I like to think that), so no harm no foul. However, I'd like to upgrade my Production box, but am waiting for this to be resolved first. MySQL 5.0.24 seems to run fine without running this script, but then again - I like to play it safe.Any ideas? David Whisler -- http://www.myspace.com/sakuradrop : forget the rest http://www.w-fenec.org/ Webzine rock/metal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication breaks
Hi, I recently upgrade our master to MySQL 5.0.24. But, I changed on option to log_bin=server-log-bin, as a result the binary log has changed from server-bin.000228 to server-log-bin.01... So now, the slave throw me an could not find first log file in binary log index. I suppose I have to do a CHANGE MASTER TO ... MASTER_LOG_FILE='server-log-bin.01', MASTER_LOG_POS=? What position should I use 0 ? 1 ? 4? Thanks for your help, -- http://www.myspace.com/sakuradrop : forget the rest http://www.w-fenec.org/ Webzine rock/metal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcc / mysql query browser
Hi, MysqlCC not being in developement anymore and not working properly with a server 5.0, we are trying to use MySQL Query browser, but there are few things which are less than efficient compared to mysqlCC. - you can't execute several queries ! The query tab executes them one by one, and if you use a script tab, you don't have any results displayed... quite annoying. - edition of results are a pain, instead of a double-click, one need to click on edit, then double-click on the cell to edit (spacebar would have been quicker) and press enter. and you can't use the arrows to move around cells when you are editing (!). To commit you need to press Apply changes. It would be nice to have be able to edit cells without clicking on Edit and be able to move with the arrows (Apply changes is a good one though). - to open a connection on another server you need to do New instance connection, it would be great to have the same thing than MySQLCC, where you can see others server in the sidebar. It's easier to move around several servers and run query through them, instead of having several separate windows. What are your usual workarounds for these ? Is there any better interface to do some queries ? -- http://www.myspace.com/sakuradrop : forget the rest http://www.w-fenec.org/ Webzine rock/metal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade returning an error
Hi, I upgraded one slave server from 4.0.23 to 5.0.24, and when I run mysql_upgrade I got the following error : ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' what can I do ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction
Hi,
2006/7/22, João Cândido de Souza Neto [EMAIL PROTECTED]:
Ok Dan.
Thanks a lot for your answer.
An other doubt about transaction is in the foreign key case. If in a
transaction i insert a certain register and get the last insert id and after
i try to insert another register using this id because this table has a
foreign key, it gives me a contstraint fail for the foreign key, that is, in
a transaction, the inserts do not get inserted until commit and i can't
insert registers in tables that has foreign key in the previous table.
You should test it :
mysql CREATE TABLE t1(id integer auto_increment, field text,primary key(id));
mysql CREATE TABLE t2(id integer auto_increment, next_id integer,
field text,primary key(id), foreign key (next_id) references t1(id))
ENGINE=InnoDB;
mysql insert into t2 values(1,1,'nope');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY
(`next_id`) REFERENCES `t1` (`id`))
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql insert into t1(field) values('my data');
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(next_id,field) values(last_insert_id(),'my data');
Query OK, 1 row affected (0.07 sec)
mysql select * from t2;
++-+-+
| id | next_id | field |
++-+-+
| 1 | 1 | my data |
++-+-+
1 row in set (0.05 sec)
mysql rollback;
Query OK, 0 rows affected (0.00 sec)
mysql select * from t2;
Empty set (0.00 sec)
In the table T2 which depends on the table T1, you are able to insert
rows even if you didn't commit your inserts (hopefully :-).
HIMH
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to look for balanced parenthesis?
2006/7/10, Joerg Bruehe [EMAIL PROTECTED]: Hi Mike, all! mos wrote: I have a complicated SQL statement with around a dozen if(this,val1,val2) embedded in it and there are even nested If clauses. I'm getting syntax errors because I'm not balancing the ( ) properly. Is there any free software out there for Windows that I can copy and paste the SQL statement into that will show me where the parenthesis are unbalanced? It doesn't have to know about SQL, I just to either highlight the parenthesis range or find out when the parenthesis become out of sync. I cannot give a list of editors that will do it, but I know that vim (vi improved) does it. It will also do syntax highlighting, but I do not know whether it knows about SQL already or would need an additional SQL syntax description. In vim, you can find matching parenthesis, with the % shorcut. Put the cursor on one parenthesis, hit %, hop you jump to the corresponding opening/closing parenthesis. (definitely a must-have for certain language). -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication stopped
Hi, recently, our slave stopped for a duplicate key error (which is a bug to me : http://bugs.mysql.com/bug.php?id=9929 ) How do you usually check automatically that slaves are up and running ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar(5) and select question
Hi,
2006/6/29, Joerg Bruehe [EMAIL PROTECTED]:
Hi Pooly, all,
Pooly wrote:
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
The rules of SQL allow you to compare even such values which you could
not assign.
So you may compare values of character string columns of different
length, and the SQL specification is that the shorter string is
effectively right-padded with blanks before they are compared
(in other words: trailing blanks are insignificant).
Ok, thanks for the explanation !
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting unique values
Hi, 2006/6/28, Chris Sansom [EMAIL PROTECTED]: I'm sure this is an elementary problem, but I can't get my head round it. I have two tables: pix and sections, the relevant bits of which are: pix (2,421 rows): picid varchar(7) not null sectionid smallint(5) unsigned not null caption text null picid and sectionid are a joint primary key caption is full text indexed sections (a mere 152 rows): sectionid smallint(5) unsigned not null title varchar(63)not null blurb text null sectionid is primary key (auto increment) title and blurb are full text indexed In pix, there may well be several instances of the same picid, but always with a different sectionid (obviously). The trouble is, this was originally set up with no intention of actually searching the tables, but now I want to. And I want to find the first instance of each picid that matches the text anywhere in caption, title or blurb, and get some other info at the same time. Oh, and for the time being it needs to be possible in MySQL 3.23.x. So far I'm doing a very simple: SELECT DISTINCT picid FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%' ORDER BY picid then as I loop through the results I'm more or less repeating the process to get the other information: SELECT p.sectionid, caption, title FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') AND p.picid = 'picid' LIMIT 1 If you know the picid previously retrieved, then the clause (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') is redundant, isn't it ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
varchar(5) and select question
Hi,
I stumbled on one issue yesterday which took me some time to figure out.
the table is :
create table tt ( PCname varchar(5) not null default '');
insert into tt values ('Centaure');
So, by mistake I inserted names which were too long for the field, but
then I tried to do queries on this particular value :
Select * from tt WHERE PCname='Centaure';
which returns obviously no result. How comes the 'Centaure' in the
SELECT is not cut has it is in the INSERT ?
--
http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version Numbers - Precedence
2006/6/24, Asif Lodhi [EMAIL PROTECTED]: Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. mathematically speaking, there no such number like 5.0.5 anyway... 5.05 perhaps... MySQL are numbered according to a X.Y.Z release number. X : is the major version, where major uplift and features are added (such as triggers, stored procedures) Y : is the minor release, where few features are added (events, partitionning) Z : is the revision number. No new features are added, only bugfix and security fix. and they are separated by dots. 5.0.5 being an beta release, I'll suggest to upgrade to the latest 5.0.z :-) -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Hi, 2006/6/21, Helen M Hudson [EMAIL PROTECTED]: Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 What you're asking does not involve join, but is a trick called group-wise maximum. Depending on your version of MySQL, there are several options to resolve this : http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html (I haven't tested it) : SELECT date, order_ref, sum(amount) FROM table s1 WHERE date=(SELECT MAX(s2.date) FROM table s2 WHERE s1.order_ref= s2.order_ref) GROUP BY s1.order_ref; the subquery get you the maximum date for each order_ref, and then you do the sum of this date. Was it what you were looking for ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql optimization
2006/6/20, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi All, I am using INNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) I hope this is just some quick copy/paste, but if in your query you have : IN (A, B) AND NOT IN (A,B), I don't think it will return much data... Is there any index on modnaptrrecord_zone.modnaptrrecord_id ? ALTER TABLE modnaptrrecord_zone ADD INDEX(modnaptrrecord_id); Where did you get the list of ids ? you may be better off using a temporary table and doing an inner or left join on it. -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The number of left join in one SQL statement.
Hi, 2006/6/16, Takanobu Kawabe [EMAIL PROTECTED]: [snip] I tried this statement without error. But Ihave some questions. 1.How many left join keywords can I use in one SQL statement if there are 5000 datas in one table? as this blog point out, its 31 or 61 depending on your version : http://mike.kruckenberg.com/archives/2006/06/limit_on_number.html And the number of rows in the table doen't have to do with this limit (only the query time :-). -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery Problem With mysql-4.0 (Works with mysql-5.0)
2006/6/5, MySQL Nexeia [EMAIL PROTECTED]: I face one problem with mysql4.0 I've two tables, one called company and the other called favorites. The relation between those two tables is CO_ID on the company table and MASTER_CO_ID on the favorites table. When I run the following query it works fine on mysql-5.0 but it give error with mysql-4.0. Query** SELECT c.MASTER_CO_ID as Expr1 FROM company LEFT OUTER JOIN (select MASTER_CO_ID,SLAVE_CO_ID from favorites where MASTER_CO_ID = '1' GROUP BY MASTER_CO_ID,SLAVE_CO_ID) c ON company.CO_ID = c.SLAVE_CO_ID GROUP BY c.MASTER_CO_ID *** MySQL 4.0.x does not support subquery. Subqueries are only supported starting with 4.1. Error with mysql4.0 #1064 - 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 'select MASTER_CO_ID,SLAVE_CO_ID from favorites where MAS *** I have two tables company and favorites, like CREATE TABLE `company` ( `CO_ID` varchar(36) NOT NULL default '', `CO_NAME` varchar(50) default NULL, PRIMARY KEY (`CO_ID`), FULLTEXT KEY `CO_NAME` (`CO_NAME`,`INDUSTRY_NAME`,`CO_ADDR1`,`CO_ADDR2`,`CO_PIN`,`CO_URL`,`CO_INFO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `favorites` ( `FAV_ID` bigint(20) NOT NULL auto_increment, `MASTER_CO_ID` varchar(36) default NULL, `SLAVE_CO_ID` varchar(36) default NULL, PRIMARY KEY (`FAV_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ; -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help querying a database of polynomials
2006/5/31, Peter Brawley [EMAIL PROTECTED]: Lew, If I have another polynomial, say the sum of terms 1,3,4, and 5, how can I quickly search this database to see if it's already been stored? SELECT DISTINCT polynomial_id FROM polynomial p1 INNER JOIN polynomial p2 ON p1.term_id=1 AND p2.term_id=3 INNER JOIN polynomial p3 ON p2.term_id=3 AND p3.term_id=4 INNER JOIN polynomial p4 ON p3.term_id=4 AND p4.term_id=5 Make sure that there is no other concurrent connection when you're doing it... (Since I expect then next step would be to insert this polynome into the DB) -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy regex replace?
2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) -- Pooly Webzine Rock : http://www.w-fenec.org/
Re: insert my_ulonglong data with C API
Hi,
2006/3/15, 古雷 [EMAIL PROTECTED]:
Thanks a lot!
But my test is not successful. Please help me.
This is my test code:
#ifdef WIN32
#include windows.h
#endif
#include mysql.h
#include stdio.h
main()
{
union ull {
unsigned char a[8];
my_ulonglong id;
} ull;
for(int i=0;i8;i++) ull.a[i]=(unsigned char)255;
char s[200];
sprintf(s,%llu\n,ull.id);
printf(%s\n,s);
return 0;
}
On Windows its output is:
4294967295
It's still a 4bytes integer.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_crt_format_specification_fields_.2d_.printf_and_wprintf_functions.asp
try :
sprintf(s,%I64d\n,ull.id);
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/11, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql set autocommit=0; mysql savepoint abc; mysql insert something mysql rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/14, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, I could not find table of innoDB in mysql. Tables in the mysql are MyISAM and should stay that way. Odds are that there is a skip-innodb in your my.cnf on your RHE, and not you XP. what produces a show status ? mysql show tables; +--+ | Tables_in_mysql | +---+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--+ I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. Could you teach me more ? Thanks and best regards, - Original Message - From: Pooly [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Monday, March 13, 2006 5:13 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/11, Truong Tan Son [EMAIL PROTECTED]: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql set autocommit=0; mysql savepoint abc; mysql insert something mysql rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- 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] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change on LEFT JOIN ON syntax in 5.x?
2006/2/20, Eric Persson [EMAIL PROTECTED]: Hi, I have a query which works fine for me in my 4.1 environment, but when moved to the 5.0.18 environment, it fails with the result below: mysql SELECT r.uid, u.username, u.image_type, count(id) AS antal, s.timestamp FROM recruits_uid r, users u, users u2 LEFT JOIN sessions s ON s.uid=u.uid WHERE r.uid=u.uid AND r.rec_uid=u2.uid AND u2.deleted=0 AND datetime-00-00 00:00:00 GROUP BY r.uid ORDER BY antal DESC LIMIT 100; ERROR 1054 (42S22): Unknown column 'u.uid' in 'on clause' mysql It seems like the table alias u is not recognized for some reason. Does anyone have any hints about this? http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html precedence of comma has been change to conform to standards. So you'll have to use arenthesis or rewrite your query. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl MySQL
2006/2/18, Mike Blezien [EMAIL PROTECTED]: Hello, MySQL version: 4.1.10a we are getting this error when attempting to connect to our MySQL database: install_driver(mysql) failed: Can't load '/usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.10: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 6) line 3 Compilation failed in require at (eval 6) line 3. Perhaps a required shared library or dll isn't installed where expected Now I recall reading something about this, something to do with the DBD version used. Or is this something too do with the MySQL version we are using? Nothing to do with your MySQL installation, it's DBD::MySQL which is not installed properly. I would suggest to reinstall it, since you have a broken dependency. (If you use debian : apt-get install libdbd-mysql-perl ) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix_timestamp + milliseconds support?
2006/2/9, Jose Antonio [EMAIL PROTECTED]: An efficient way to store time is as UNIX_TIMESTAMP (4 bytes) instead of using DATETIME data type (8 bytes). We were using this technique to save the time in our database. Now we need to support millisecond resolution as we need to store data comming 8 times per second, that is, every 125 milliseconds. Have you already face this problem and come to a solution you are happy with? a smallint column should be acceptable for storing millisecond (that's what we use), but depending on the way you need to manipulate those millisecond, a tinyint could be acceptable as well (you only store millisecond with a precision of 1/8 second). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Last access time of a table
Otherwise, you could try to use the binlog. The filesystem sure would be easy though... for mysql 5.0 show table status -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
2006/1/25, Nathan Gross [EMAIL PROTECTED]: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Estimated Drive Space Requirements
HI, 2006/1/25, Cummings, Shawn (GNAPs) [EMAIL PROTECTED]: If I have about 2Gb of raw text data to import everyday -- can I expect that to take up about 2Gb in a mySQL database ... slightly more.. double? It all depends on your columns, indexes, and so on... http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html Without any more details, we can't help you precisely. Pretend there's no indexes for now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHAR vs TEXT and fast Row Updates
Hi, 2006/1/17, Karl Pielorz [EMAIL PROTECTED]: Hi All, 1. Fixed length CHAR fields are quicker to update than VARCHAR fields (because the field size is constant) There is no advantage if not all your field in your table are fixed size. as soon as you add a text/blob column, you loose the fixed row length. Keep in mind that index will also be fixed-length, and it can be more efficient to have varchar to have quick select. Do we get any 'saving' by using a TEXT field, and pre-populating this with say 2K of 'spaces' when we create the 5,000 rows - and then ensuring that the UPDATE operation always writes 2K of text to the field? - e.g. will this avoid MySQL having to 'free up' the space for the field, then re-allocate 2K again for it. space is not reclaimed after deletion until you run an : optimize table. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stepping into libmySQL.dll with VC6
Hi, 2006/1/16, Jan M [EMAIL PROTECTED]: Hi, How do I set things up so I can step into the libmySQL.dll from my C code using VC6? Binary download for Win32 doesn't include a debug verison of the dll (AFAIK), so you'll have a recompile by yourself. Download the source, load the projet, chose the debug version, and off you go. Quiet useful to debug buggy query strings :-D -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Histogram from tables.
Hi, 2006/1/13, Mike Martin [EMAIL PROTECTED]: I have a large table of filenames and creation dates from which I want to produce a histogram. SELECT year(date), quarter(date), count(0) FROM pics WHERE date(date) '2000' AND date(date) ' 2005' GROUP BY year(date), quarter(date) [snip] I want this: ++---+--+ | year(date) | quarter(date) | count(0) | ++---+--+ | 2001 | 1 |0 | | 2001 | 2 |0 | | 2001 | 3 | 34 | | 2001 | 4 |0 | | 2002 | 1 |2 | | 2002 | 2 |0 | | 2002 | 3 |0 | | 2002 | 4 |1 | | 2003 | 1 |0 | | 2003 | 2 |1 | | 2003 | 3 |1 | | 2003 | 4 |3 | | 2004 | 1 |1 | | 2004 | 2 |1 | | 2004 | 3 |5 | | 2004 | 4 |1 | ++---+--+ Someone will surely come up with a better solution, but I would do something like that : Create a table date with one date for each quarter (or, if you do a script a more complete table, with all working days for instance), do a left join on it with year and quarter. SELECT year(dates.date), quarter(dates.date), count(pics.date) FROM dates left join pics on year(dates.date)=year(pics.date) and quarter(dates.date)=quarter(pics.date) WHERE year(pics.date) '2000' AND year(pics.date) ' 2005' GROUP BY year(dates.date), quarter(dates.date) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join record in one joined table and not in another
Hi, 2006/1/12, Vincente Aggrippino [EMAIL PROTECTED]: On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: That fixed it... Thank you! But I don't understand how. Isn't my implicit inner join the same as the explicit one you used? I read Join Syntax in the ref. manual. Is it related to the new order of precedence for the JOIN operator? comma operator has lower precedence than join now : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5 - disk bound - fixed
2006/1/11, George Law [EMAIL PROTECTED]: Hi All, [snip] I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? I believe CREATE TABLE newtbl SELECT blah... is what you're after : http://dev.mysql.com/doc/refman/5.0/en/create-table.html FTFM : You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: autoincrement for year
2005/12/28, Salvatore Celsomino [EMAIL PROTECTED]: Hi, it is possible to create a field autoincrement for year. example: 1/2005 2/2005 ... 10500/2005 new year-- 1/2006 2/2006 This could be of interest : http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html So, if the year is generated by your application or with YEAR(), you could do what you want. (For MyISam and BDB tabel types) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSTALLING MYSQL 5.0 ON LINUX DEBIAN 2.8
2005/12/22, John Galatti [EMAIL PROTECTED]: All I am trying to build the mysql 5.0 from the source When I run the configure scripts it error out saying it can not find termcap data base also, can not find tegenent in any library I am running under debian linux 2.8 you mean Debian with a linux kernel 2.6.8 ? then you're running sarge. Did you install the dev packages ? apt-get install termcap-dev -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1 full table (nearly) - best practice to alter?
Hi, 0. stop the web server or avoid hitting the db ! 1). Backup everything! :) the mysqldump should suffice. 2). do the alter The alter does already a create table with new data and then exchance table. You need 2x the storage space though. 3. do the opposite of 0. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: differenced backup from sql dumps
2005/11/14, [EMAIL PROTECTED] [EMAIL PROTECTED]: We are making whole database sql dump every night. Now I have a bunch of sql dumps, that covers much space. Is there opensource tools with whom I could make one smaller differencial backup file with possibility to get dump from every signle day? Any ideas?:) diff ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL Support '=' Sign?
2005/11/11, The Nice Spider [EMAIL PROTECTED]: Does MySQL support: SELECT ID_TAG= ID FROM TABLE rather than select ID TAG_ID from Table. I need this for MS SQL Compabilty. Did you try the AS keyword ? Select ID As ID_TAG from TABLE -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: vpn connectivity
2005/11/8, prathima rao [EMAIL PROTECTED]: hai all , im using mysql 4 version we are trying to connect inter units thriugh VPN will my database work and how? You just need to open the port 3306 on your firewall. No other step should be requiered. p rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN problem
2005/11/5, Guy Brom [EMAIL PROTECTED]: I have the following tables: languages language_id int, language_title varchar(80) objects object_id int object_language_id int object_title varchar(100) I want to select ALL available languages, and match the translated object (if it is exists) for a specific object_id. If it does not exists for that specific language_id, I want to have NULL. Have a try with : Select language_id,language_title,object_id,object_title FROM languages LEFT JOIN objects ON objects.object_language_id=languages.language_id -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
2005/11/4, Marc Pidoux [EMAIL PROTECTED]: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... A setup of ASP/MySQL could be a right option for you if you already know ASP. I'm not sure if it's possible though (using ODBC ?). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
2005/10/20, Heikki Tuuri [EMAIL PROTECTED]: Andrew, how about using triggers to recompute materialized views? A challenge is to write an automatic program that can compute the required triggers based on the view definition. The materialized view would be a normal table. Triggers would update it. Regards, Better, mark this view (or particular rows if it's not too expensive) as dirty and recompute it only on access, you may spare few cycles... -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deadlock found when trying to get lock; try restarting transaction
Hi,
I had that error for quiet a long time, and I usually restart the
transaction, but sometimes I have to do it 2 or 3 times, and I don't
really understand how it can happen.
I've strip down an example, that does basically :
BEGIN;
SELECT id FROM ttt WHERE id=7 FOR UPDATE;
INSERT INTO ttt(id) VALUES (7);
DELETE FROM ttt WHERE id=7;
COMMIT;
I run 10 instances of the program in parallel and I get the error :
Deadlock found when trying to get lock; try restarting transaction.
The isolation level is the default one.
My understanding of the SELECT ... FOR UPDATE is that I should not get
that deadlock, all transaction should be waiting on this select. From
the manual :
A SELECT ... FOR UPDATE reads the latest available data, setting
exclusive locks on each row it reads.
All instances should select the latest data, or wait until the lock is released.
Does anyone have pointer for a better explanations ?
Here is my program which I run in parallel with :
for i in 1 2 3 4 5 6 7 8 9; do ./test_mysql $i done
#include mysql/mysql.h
#include stdio.h
int main(int argc, char **argv)
{
MYSQL *mysql;
int insert =0;
my_init();
mysql = mysql_init((MYSQL*)NULL);
if(! mysql_real_connect( mysql,
127.0.0.1,
root,
,
test,
3306,
NULL,
CLIENT_COMPRESS) ) {
printf(Connexion failed.\n);
mysql_close(mysql);
} else {
int ret;
printf(%s : create table\n, argv[1]);
ret = mysql_query(mysql,
CREATE TABLE IF NOT EXISTS ttt
( id integer unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) Engine=InnoDB;);
if ( ret ) {
printf(%s : Creation failed %s\n, argv[1],
mysql_error(mysql));
return 1;
}
printf(%s : Begin\n, argv[1]);
ret = mysql_query(mysql, BEGIN);
if (ret) {
printf(%s : Begin failed %s\n, argv[1],
mysql_error(mysql));
return 1;
}
printf(%s : Begin ok\n, argv[1]);
printf(%s : Select for update\n, argv[1]);
ret = mysql_query(mysql,
SELECT id FROM ttt WHERE id=7 FOR UPDATE);
if ( ret ) {
printf(%s : select failed : %s\n, argv[1],
mysql_error(mysql));
return 1;
} else {
MYSQL_RES *res;
res = mysql_store_result(mysql);
if ( res mysql_num_rows(res) ) {
printf(%s : found a row\n, argv[1]);
insert = 0;
} else {
printf(%s : found no row\n, argv[1]);
insert = 1;
}
if ( res )
mysql_free_result(res);
}
printf(%s : Select for udate OK\n, argv[1]);
printf(%s : sleep\n);
sleep(1);
/* should be ok to check and not fire a timeout */
if (insert ) {
printf(%s : insertion \n, argv[1]);
ret = mysql_query(mysql,
INSERT INTO ttt(id) VALUES (7));
if ( ret ) {
printf(%s : insert failed : %s\n,
argv[1], mysql_error(mysql));
return 1;
}
printf(%s : delete it \n, argv[1]);
ret = mysql_query(mysql,
DELETE FROM ttt WHERE id=7);
if ( ret ) {
printf(%s : delete failed : %s\n,
argv[1], mysql_error(mysql));
return 1;
}
}
printf(%s : commit\n, argv[1]);
ret = mysql_query(mysql, COMMIT);
if ( ret ) {
printf(%s : commit failed : %s\n, argv[1],
mysql_error(mysql));
return 1;
}
printf(%s : Commit ok\n, argv[1]);
}
return 0;
}
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
2005/10/6, Ed Reed [EMAIL PROTECTED]: I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8|5 | NULL | +++--+ | 8|4 | a | +++--+ | 8|6 | NULL | +++--+ | 8|6 | a | +++--+ | 8|5 | a | +++--+ | 8|4 | b | +++--+ | 9|1 | NULL | +++--+ | 9|2 | NULL | +++--+ | 9|1 | a | +++--+ | 9|3 | NULL | +++--+ | 9|2 | a | +++--+ | 9|1 | b | +++--+ | 9|4 | NULL | +++--+ | 9|4 | a | +++--+ | 9|2 | b | +++--+ | 9|1 | c | +++--+ | 10 |1 | NULL | +++--+ | 10 |1 | a | +++--+ | 10 |2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b| +---+ | 8-5a| +---+ | 8-6a| +---+ | 9-1c| +---+ | 9-2b| +---+ | 9-3 | +---+ | 9-4a| +---+ | 10-1a | +---+ | 10-2| +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
2005/10/3, s. keeling [EMAIL PROTECTED]: I'd like to add a bit of history data to a table (who changed a record last, and when it was last changed). Is this the way to do it? alter table MEMBERS add CHG_BY varchar(3) alter table MEMBERS alter CHG_BY set default sbk alter table MEMBERS add CHG_DATE date alter table MEMBERS alter CHG_DATE set default CURRENT_DATE Whoever next ends up with this can set CHG_BY's default to their initials and carry on from there. It will be best to ensure the good value in your application, I'm not sure everyone is willing to fill more fields in a query that track him back. You better go with an extra table to track all changes, and not only the last one. Something like members_changes (member_id, chg_by, chg_date) btw, you could do : alter table MEMBERS add CHG_BY varchar(3) not null default sbk, add CHG_DATE date not null default CURRENT_DATE on update current_date (not tested) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Regular expression
2005/10/1, Binay(Local) [EMAIL PROTECTED]: Hi Can any body tell me how to strip all the html characters from a text column while querying? MySQL can't do it for you, you'll have to program it at your application level. (PHP has strip_tags, Perl and QT have regular expression ) Thanks Binay -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore db from mysqldump I get different databases size
2005/10/1, Lorenzo Sicilia [EMAIL PROTECTED]: Hi to all, I have a database online with innodb myisam tables. I use this option when I do a daily backup: #mysqldump -c -e -Q -u --lock-tables --flush-logs --opt myDB -u MyUser -p db.sq when I restore my db with this command: # mysql -u root -p -D myDB_restored db.sql I get a new databse but the size it doesn't match. I do a diff between myDB_restored.sql and db.sql and all match. Then why my db in production it's more heavy of my restored db? Orginal size 3.1 MB the new size 2.7 MB. any idea? They may not be ordered in the same way, and may have empty spaces. If you optimized all tables in both database, you would then get the same size. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Browser
2005/9/30, Sujay Koduri [EMAIL PROTECTED]: I am not saying MysQL Query Browser is anyway bad or inferior, but as for my experience sqlyog is very good. There is a free version which you can use for executing SQL queries, ofcourse you will be stripped of some advanced features. You can run multiple queries at once using shift+F5. I suggest you to try this out once. the no longer supported Mysql Control Center, can do it as well. It supports single/multiple query without tricks. You can even directly edit your dataset (and not do the annoying : edit+ do your stuff + apply changes of MysqlQuery Browser ) sujay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 7:14 PM To: Rob Agar Cc: 'Mysql ' Subject: RE: MySQL Query Browser Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Hi, 2005/9/23, [EMAIL PROTECTED] [EMAIL PROTECTED]: Jeff [EMAIL PROTECTED] wrote on 09/23/2005 09:57:06 AM: Yes, I meant exactly that. Within each MySQL server is a special database called `mysql`. That is the database that contains the tables of all of the user login and permission information for the server (and several other important bits of system-wide metadata). None of the tables in that database can be converted to InnoDB. That would be a bad thing. The tables of every OTHER database on the server (including yours) are eligible for InnoDB conversion so long as you do not want to use fulltext searching. If you need a FT index, you have to keep that table as MyISAM for now (they are working on enabling FT indexes in InnoDB but there is no release date yet) And GIS as well, IIRC: http://dev.mysql.com/doc/mysql/en/spatial-extensions-in-mysql.html (just to be picky) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar vs char speed improvement
2005/9/27, Ow Mun Heng [EMAIL PROTECTED]: Is there any doc looking at benchmarks of a database which is populated entirely with fixed length char compared to variable character lengths? I know using char is preferred over varchar when it comes to speed. Is there any available benchmarks available? I guess benchmarks depends on your data... varchar take really less space, and so is faster to read from disk, so you could improve speed in having varchar ! But since it cause dynamic row format, it can makes think slower. Pointers where would be appreciated. -- Ow Mun Heng Gentoo/Linux on DELL D600 1.4Ghz 1.5GB RAM 98% Microsoft(tm) Free!! Neuromancer 10:38:25 up 2 days, 15:24, 6 users, load average: 0.71, 0.56, 0.35 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
Hi,
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
Try with mysql_fetch_array
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
or it's likely that mysql_result retrieve an array of rows
(well-ordered), but fetch it by using a string for the index.
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To multi thread or NOT to multi thread?
2005/9/27, Lefteris Tsintjelis [EMAIL PROTECTED]: Hi, What makes me wonder is that the same test, with the code stripped down, to my surprise, is significantly faster that the multi threaded one, no matter how many times I run the tests. I am including the code for both tests I run. Since I couldn't find a good example of mutex locking the following one is something that worked for me. However, I am not sure if its as optimized as it should be, so I would appreciate an expert's opinion about this. Is this a good example of mutex locking? Are there any other better ways for this? Is this an OS or MySQL issue? I am currently running 4.1.14 on a FreeBSD5 box. You ran several queries with multiple thread, fine, but they are all serialised over one connection, so you get all the overhead of locking and thread-creation, for no advantage... So that's the result expected. (So, yes forthe troll, it's an OS issue, threads creation are somewhat slow on FreeBSD :) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Hi, 2005/9/26, Peter Brawley [EMAIL PROTECTED]: Claire, I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. other solution, do your query in all case and check for the return error (if any). mysql alter table board add message varchar(255) not null default ''; ERROR 1060 (42S21): Duplicate column name 'message' So, if you get back that error, the column already exists. So if it exists the table is left untouched, otherwise it does what you want. (yeah, Information_schema would be better, but 5.0 is in gamma) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Must mysql_free_result be called after mysql_store_result in case of NULL?
2005/9/24, Lefteris Tsintjelis [EMAIL PROTECTED]:
Hi,
I have been searching for this for a while without finding a
good answer.
In the following example, MUST I call mysql_free_result() in
case mysql_store_result(sql)==NULL or not?
Is the following valid in case of NULL?
query=update/insert ...
if (mysql_query(sql,query)!=0)
return(-1);
if(mysql_store_result(sql)==NULL)
return(0);
else {
...
}
if you do that, you don't retrieve your result since you don't set any
variable with it
if ( (res = mysql_store_result(...))==NULL ){
//no result set (update,delete, insert, wrong select...)
return 0;
if ( res ){
mysql_free_result(res);
res = NULL;
}
So you have to call a mysql_store_result for a select statement, but
otherwise, you don't have to. And if mysql_store_result return null
there is nothing to free, but you have to check for errors. (if you
fre that null pointer, what do you think will happens ?)
http://dev.mysql.com/doc/mysql/en/mysql-store-result.html
mysql_free_result(result);
Thnx,
Lefteris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert subquery
2005/9/23, Gordon Bruce [EMAIL PROTECTED]: What am I missing INSERT INTO table1 (column names.) SELECT VALUES.. FROM table2 WHERE primary id = insert value You will have to put in your real table name and column names. nice and easy :-) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multithread handling of Connect/Close
Hi,
2005/9/24, Lefteris Tsintjelis [EMAIL PROTECTED]:
Sujay Koduri wrote:
I think mysql does not allow multiple threads to act on the same connection.
You have to create a connection pool and pick one for each thread
You can use one connection for all your thread.
Just be sure to properly enclose it with a lock/mutex, and build you
query thread-safe.
for connecting :
sql.lock
sql.connect
sql.unlock
for query :
sql.lock
mysql_thread_init()
sql.query
sql.sotre_result
mysql_thread_end()
unlock
Either that or keep handling them on a per thread basis but do I have to also
do mysql_server_init/end on a per child then, or just once in the parent proc?
while loop childid
{
fork();
...
mysql_server_init(...);
mysql_real_connect(...)
...handle thread...
mysql_close(...)
mysql_server_end();
...
}
exit(0);
or should it be something like:
mysql_server_init(...);
while loop childid
{
fork();
...
mysql_real_connect(...)
...handle thread...
mysql_close(...)
...
}
mysql_server_end();
exit(0);
Thnx,
Lefteris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
what about : http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html 2005/9/22, Ananda Kumar [EMAIL PROTECTED]: Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4 on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc /odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r- 3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc/odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r-3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query when calling stored procedure
Hi, 2005/9/21, Jasper Bryant-Greene [EMAIL PROTECTED]: Martijn Tonies wrote: I have a stored procedure defined as follows: CREATE PROCEDURE `album`.`getUser`( IN userID INT ) READS SQL DATA DETERMINISTIC SQL SECURITY INVOKER SELECT * FROM users WHERE id=userID LIMIT 1; Sometimes when I execute this stored procedure with, for example: CALL getUser(7); I get the error Lost connection to MySQL server during query. This only ever happens with stored procedures, never with any normal kind of query. I am using MySQL 5.0.12-beta-log on Gentoo Linux x86. And what is your question? Fairly obviously, how do I fix it!? ;) Most probably, a server crash... Can you be a little bit more specific please? The MySQL server is working absolutely fine for everything else and also continues to respond to connections and queries perfectly after getting that error. I think he means, that you should try the official binaries, and this still happens, file a bug report because 5.0 is still in Beta stage. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() 2 different connections
Hi, It is Linux Slackware 10.1 with MySQL 4.1.4 installed from source... I have tested it too (same program and same version of MySQL) with FreeBSD and the result is almost the same, the difference is that Linux craches with glib message and in FreeBSD just sends a warning that the results have been already free... 4.1.4 is beta version, isn't it ? I'll suggest you try the latest binary for 4.1 available for your setup. I suspect that the current version of MySQL frees the resources automatically, no, it does not :-) in FreeBSD the warning was more explicit, it said something like trying to free again a result chunk... this one performed a single query and a single result_free() so there was no place for a mistake... again, showing _real_ code would help, or a strip-down code that crashes. The MySQL api does not free ressources automatically, so if you have the crash on two different system, I would think the bug comes from your code. That would leave a nice memory leak indeed... eventually, recompile and set a break-point before calling mysql_free, and look at all variables. MySQL internal variables? to see the status before? that's a good idea, I will do it, thanks for your comments and will post it as soon as I get results :-) nop, you MYSQL_RES, and it's value. did you set to NULL after freeing it ? that would help. Regards P.S. I already posted it to bugs.mysql.com but still do not get response again, I doubt it's a bug in the api, I use various 4.0.x, 4.1.x daily and several OS and I've never had a crash in it (except from my own mistakes, non-null terminated query strings, threads messing up, freeing twice a result, non-allocating of result). the API is pretty robust :-) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() 2 different connections
hi, I have 2 different result sets, since are two different connections to the server, simultaneous but independent (two handles, two connections, two different queries) Today was probing one of my programs with just one single connection and mysql_free_result() still crashes even with just one set of results, no matter how I use it, it always crashes :-( what is the code you're using ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Failure to install on Solaris.
Hi, /usr/local/bin/ld: cannot find -lpthread collect2: ld returned 1 exit status gmake[4]: *** [mysql_tzinfo_to_sql] Error 1 gmake[4]: Leaving directory `/scratch/hgs/mysql-4.1.13/sql' gmake[3]: *** [all-recursive] Error 1 gmake[3]: Leaving directory `/scratch/hgs/mysql-4.1.13/sql' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/scratch/hgs/mysql-4.1.13/sql' gmake[1]: *** [all-recursive] Error 1 gmake[1]: Leaving directory `/scratch/hgs/mysql-4.1.13' gmake: *** [all] Error 2 neelix hgs 57 % ls /usr/lib/*pthread* /usr/lib/libpthread.so /usr/lib/llib-lpthread /usr/lib/libpthread.so.1 /usr/lib/llib-lpthread.ln neelix hgs 58 % Any suggestions as to how I get around this and get the whole thing installed in /usr/local/mysql-4.1.13 ? Have you installed the development package for the pthread library ? I guess you're missing libpthread.a which is needed even for a dynamic linking, the linker can't do it's job here. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() 2 different connections
Hallo,
in a very small representation the program is this
*** dbmail and dbmail2 open 2 different connections, one accesses
DBM_ADDRESSES table and the other accesses DBM_NAMES table. The first one
retrieves addresses from the table DBM_ADDRESSES, the second one a relationed
names to the addresses from DBM_NAMES. Not all addresses have name set, so
for questions of space I separated into tables, just 10% of addresses have a
name and it would be waste of space having that column in the same table
(90%+ of over 50,000 rows), so I created a new table for the names and linked
them to a corresponding address via an 'id'
Are your two tables in the same database ?
If yes only one connection if enough, and you could use a join query
to do it, thus having a speed improvement of several magnitudes.
1. connect dbmail and dbmail2 to server
2. dbmail.query: SELECT id,address from DBM_ADDRESSES WHERE ...
sp_id = atoll(record[0]); // stores the id in 'sp_id'
3. while ( dbmail retrieves rows from query at step 2 ) {
dbmail2.query:
3.1 SELECT name FROM DBM_NAMES WHERE id=sp_id(from step 2)
3.2 if ( name exists for id=sp_id )
do something with DBM_ADDRESSES.address,DBM_NAMES.name
else
do something with DBM_ADDRESSES.address
3.3 free results from dbmail2.query (inside while)
}
4. free results from dbmail.query (1st query before while) ***CRASH***
5. disconnect dbmail and dbmail2 connections
The crash is in step #4 after all data is retrieved successfully, but why?
Have you checked that you free the correct result ? set it to NULL
after used, and add a test for nullity, it may help.
What version of the API are you using ? which OS ?
Have you check bugs.mysql.com ?
1. query: SELECT ...
2. while ( fetch rows ) { use the results }
3. mysql_free_result()
it crashes too, this model is very (really very simple) and does not leave
space for errors in code and still fails on the free function...
pseudo-code won't crash as far as I can tell :-)
I've asked on a linux list and somebody told me just to avoid using
mysql_free_result() if the program works that way ('cos the results maybe are
being already free by the library), but I don't think it is a solution, I
want to know what happens, if really the set is free automatically by the C
API functions after reading it or not, and if so, why the documentation tells
that it is a *must* call the free function after the whole set of rows is
retrieved...
That would leave a nice memory leak indeed...
eventually, recompile and set a break-point before calling mysql_free,
and look at all variables.
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() WITHOUT mysql_fetch_row()
Hi,
always reply to the list please
2005/9/19, Miguel Cardenas [EMAIL PROTECTED]:
I'm a bit confused. If you just need to know if there is data matching
a criteria, a count(*) is enough and will do absolutely the same thing
that you want, and spare you the mysql_store_result with a whole
dataset.
count(*) as nothing to do with knowing is there is data in the table or
not... If I'm still wrong, could you provide a sample query ?
Well maybe am making a storm in a glass trying that and possibly there is
another solution...
There is a table like this:
--
| id INT | list CHAR(16) |
--
I have (id,list) pairs, there may be duplicate id's or list's separately, but
together may exist unique pairs.
a,1 --- ok
a,2 --- ok
b,1 --- ok
b,2 --- ok
a,1 --- error, duplicated pair
so, in pseudocode I do this
lets supose I want to insert (x,something)
while ( retrieve data from file ) {
1. select id,list from mytable where id=x and list=something
2. use_result()
3. fetch_row()!=NULL ? (data exists already)
yes: don't do nothing
no: insert into mytable values(x,something)
...
}
the process is repeated thousands of times, so, retrieving all data in every
loop would make a big difference, 'cos that I use use_result() instead of
store_result() thas would retrive all data in every loop...
Have you try :
select count(*) from mytable where id=x and list=something
if count ==0, it's like fetchrow==NULL from your solution, but without
all the fuss about use_result();
I am not very experienced in mysql, with a 'UNIQUE' option for a field allows
only one unique field, but in this case fields may be duplicated, what can
not be duplicated are pairs, 'cos that first I see if it already exists on
the table before insertion.
You may run into problem if two process access your table at the same time.
First solution would be to : lock the table, check the existance,
insert if it's ok, unlock the table.
Second solution :
ALTER TABLE mytable ADD UNIQUE(id,list)
which make a unique index on two field. inserting a duplicate value
would give you back an error and let the table untouched.
Maybe you have a suggestion to do the same in another way.
Thanks
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select ALL rows with WHERE statement
2005/9/18, Jasper Bryant-Greene [EMAIL PROTECTED]:
Alvaro Cobo wrote:
Thanks Pooly and Peter:
The problem is that in the same php page I would like to choose either the
calculation from an especific year or the calculation from the whole table
using URL.
The same page can easily handle both.
?php
$sql = SELECT * FROM mytable;
if(isset($_GET['year']) !empty($_GET['year'])) {
$sql .= WHERE year= . intval($_GET['year']);
}
$result = mysql_query($sql);
?
Well, if you have control on everything, this approach would be the
best, no trick, easy to understand and maintain.
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() WITHOUT mysql_fetch_row()
2005/9/17, Miguel Cardenas [EMAIL PROTECTED]: In the last episode (Sep 17), Miguel Cardenas said: I have a technical doubt, very simple but not mentioned in the manual... What happens if I call mysql_free_result() after performing a query but without doing mysql_fetch_row() ??? It may sound strange, but just need to know if a data row exists more than the data itself... A faster way would be to run a SELECT count(*) WHERE ... query, and check the result. Since you are not selecting any data, mysql is more likely to be able to use index lookups to speed the query up. Well, in fact don't want to check if there is data already inserted on the table to make condition TRUE, what I want is to do this: SELECT id,data FROM table WHERE id=XXX but, just want to check if it the search found something and then free the resources, not to retrieve the data... in short words: 1. search rows which id=XXX 2. if mysql_num_rows() 0 then CONDITION=TRUE else CONDITION=FALSE 3. mysql_free_result() WHY? well, this operation will be repeated over 100,000 times and only need to know if the specified data exists, the difference between just know and retrieve 1 time and 100,000 times surely would be significative if I try 1. search rows which id=XXX 2. if mysql_fetch_row() then CONDITION=TRUE else CONDITION=FALSE 3. mysql_free_result() Specifically, my doubt is, what happen if I call mysql_free_result() if I don't call mysql_fetch_row() before, or if not all rows are read before calling it... http://dev.mysql.com/doc/mysql/en/mysql-free-result.html it does not says : you have to retrieve all rows before freing it. So you should be able to free the result. Otherwise it's a bug or undocumented behaviour. But why don't you use the Count(*) solution ?? that's exactly what you need IMHO. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() 2 different connections
Hi,
while ( my1.fetch ) {
my2.query + my2.store
if ( my2.fetch ) {
report = my1 + my2 results
} else {
report = my1
}
my2.FreeResult --- free for every my2.query inside loop
}
my1.FreeResult --- free for unique my1.query
One My2.store == One My2.free.
you're doing it more thant once, it can't work.
from the fine manual :
http://dev.mysql.com/doc/mysql/en/mysql-store-result.html
You must call mysql_free_result() once you are done with the result set.
but in you're code you're still using the result set !
--
Pooly
Webzine Rock : http://www.w-fenec.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_free_result() WITHOUT mysql_fetch_row()
2005/9/18, Miguel Cardenas [EMAIL PROTECTED]: http://dev.mysql.com/doc/mysql/en/mysql-free-result.html it does not says : you have to retrieve all rows before freing it. So you should be able to free the result. Otherwise it's a bug or undocumented behaviour. But why don't you use the Count(*) solution ?? that's exactly what you need IMHO. No, I don't need to know if exists *any* record, I need to know if it exist one specific record or set of records, but not to retrieve them, just need to know if it/they exist to take a decition on how to insert the following ones... if I need only to know if there is data on the table the COUNT(*) would be enough... I'm a bit confused. If you just need to know if there is data matching a criteria, a count(*) is enough and will do absolutely the same thing that you want, and spare you the mysql_store_result with a whole dataset. count(*) as nothing to do with knowing is there is data in the table or not... If I'm still wrong, could you provide a sample query ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS subquery optimization
Hi, Now as expected it's an dependent subquery and makes use of the index on document_id. BUT: If we change the SELECT id in the subquery to SELECT document_id or SELECT 1, we get: *** 1. row *** [...] *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Using index Note the Using index! I played around with it and it really makes a huge performance difference - in my case the file table contains a lot of large blobs, is fragmented and I'm running weak hardware. Seems as if using index is not just (irrelevant or wrong) EXPLAIN output but is what is really going on? From the manual : - Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. So you should save an extra seek from the disk I guess. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select ALL rows with WHERE statement
2005/9/17, Alvaro Cobo [EMAIL PROTECTED]: Hi all: I've searched into the MySQL manual but I can't find the solution to this very silly question. I have a select query where I need to filter the data using the WHERE statement. But also need to have an option to show all the data in the table. For example (something like...): SELECT * FROM `tbl_table1` WHERE field1 = 'Test' #To show all the rows where field 1= 'Test' But also have the choice to select all the rows using something like: SELECT * FROM `tbl_table1` WHERE field1 = * or ALL or % #Select all the fields. You need to select all rows, but you have only control on the condition on flied1, I am right ? If you can input %, it should do the trick. select * from table WHERE field1 like '%'; Why can't you do just select * from table ?? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and myisam and db optimising
2005/9/16, OKAN ARI [EMAIL PROTECTED]: I have a web site that have 100unique users online at the same time. I will optimize DB structure. 1. Is there anysource about mysql optimization and performance tricks? you might want to look here : http://dev.mysql.com/doc/mysql/en/query-speed.html 2. I can change the DB type, which is the best for high mysql traffic web sites, innoDB or MyIsam? http://dev.mysql.com/doc/mysql/en/internal-locking.html It depends on your website. If it's massive select over few updates/inserts, go for MyISAM, unless you need transaction. Check for slows queries. If you don't have access to the slow query logs, the best is to log query time on a per page basis (or per script/template which is better, depending your website). check the field type, no nee to have an integer to store a state value which is 0 or 1 for instance. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and myisam and db optimising
Hi, always reply to the list, you'll get better answers. 2005/9/16, OKAN ARI [EMAIL PROTECTED]: My actual DB isoptimized well about field types. I am using tinyint If i need etc;) But my problem is about examining logs, because 1. I dont' want to know how to reach mysql logs? Can you explain me how can I examine detailed mysql logs? 2. Can you basicly define transaction and its advantages and disadvantages? Thank you - Original Message - From: Pooly [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 16, 2005 5:13 PM Subject: Re: innodb and myisam and db optimising 2005/9/16, OKAN ARI [EMAIL PROTECTED]: I have a web site that have 100unique users online at the same time. I will optimize DB structure. 1. Is there anysource about mysql optimization and performance tricks? you might want to look here : http://dev.mysql.com/doc/mysql/en/query-speed.html 2. I can change the DB type, which is the best for high mysql traffic web sites, innoDB or MyIsam? http://dev.mysql.com/doc/mysql/en/internal-locking.html It depends on your website. If it's massive select over few updates/inserts, go for MyISAM, unless you need transaction. Check for slows queries. If you don't have access to the slow query logs, the best is to log query time on a per page basis (or per script/template which is better, depending your website). check the field type, no nee to have an integer to store a state value which is 0 or 1 for instance. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unclear on UPDATE versus INSERT, and a simple query that is not working
2005/9/15, Dave [EMAIL PROTECTED]: MySQL General List, Server specifications: MySQL 4.1.3-beta, phpMyAdmin 2.5.7-pl1, PHP 4.3.8 My specifications: MySQL beginner, PHP intermediate, HTML and CSS advanced. The situation: I have two tables, one old, and one new. In both tables I have a column called active, which is either 0 or 1, to denote whether a user is to receive email or not. I am migrating all the data from the old table to the new. I want to make sure that the active column is identical, so I simply want to copy the fields from the old table to the new, overwriting what may exist in the new. I want to match the fields by using the member ID, called id in the old table and MEMBER_ID in the new. This is the statement I thought would work. UPDATE forum_members( active ) SELECT active FROM members WHERE forum_members.MEMBER_ID = members.id I'm sure other on the list will explain in details, but I'll do short. I understood that forum_members is the new table, members the old one. So : UPDATE forums_members,members SET forums_members.active=members.active WHERE forums_members.member_id = members.id -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [newbie] moving mysql db to new server
2005/9/15, Julien vander Straeten [EMAIL PROTECTED]: hi all, my machine crashed and i'd would like to restore my mysql databases on my new system. i'm having a backup of the entire drive, but no dump. my system is mac os x.3 server. is there a way to dragdrop some folders to my new system? If you use MyISAM Table format, you could do that indeed. shutdown server Look for your MySQL Folder and just drag/drop the folders (the ones containing .frm, .MYD and .MYI files) in this directory (except mysql and test) into you new destination. restart server If it's InnoDB, I have no idea... thanks for your help :-) julien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of DB with many tables
2005/9/1, Chris Cutler [EMAIL PROTECTED]: Hello, My apologies for repeating a question asked earlier[1] but the question and the answer were a little vague: Does the number of tables in a database affect MySQL's performance? Specifically, if I have a database with 1,000 tables in it, will it be slower than usual due to the number of tables? What about a database with 10,000 or 100,000 tables? As one section of the MySQL manual points out[2]: If you have many MyISAM or ISAM tables in a database directory, open, close, and create operations are slow. they are slow due to the underlying filesystems (AFAIK) But what about INNODB? And are there table engine-independent implementation details which might cause performance problems for a database with many tables? il you only have one innoDB tablespace, it should not be a problem, but for the option to have one file per innodb table, that's the same conclusion. Thanks, Chris [1] http://lists.mysql.com/mysql/174461 [2] http://dev.mysql.com/doc/mysql/en/creating-many-tables.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Subquery bug or am I doing something wrong?
Hi, mysql SELECT criterio, idSite - FROM dominios_propios_completos - WHERE criterio NOT IN ( - SELECT criterio_pub - FROM sites_criterios - ); Empty set (0.05 sec) Do you have NULL values in sites_criterios.criterio_pub ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT alternative
2005/9/1, Vladimir B. Tsarkov [EMAIL PROTECTED]: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. FTFM : For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. but if you want to be fully compatible with many others RDBMS, go for something like adodb, or some others DB interface in PHP Thanks! -- Good Luck! Vladimir Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please I need inputs on Lost connection to MySQL server during query
Hi, what is the error in your error log ? Does your server crash for every query it makes ? Or only some ? Are you using the official binary from MySQL or the one used for you distro vendor ? Are you using UDF ? ... We need more inputs ;-) 2005/9/1, JM [EMAIL PROTECTED]: Hi all, Ive read this link http://dev.mysql.com/doc/mysql/en/gone-away.html; hoping this has something to do with the error... Ive already checked based on the page the list of roots of error to produce gone-away.. w/c i hope leads to Lost connection to MySQL server during query and non of it is present. One thing Ive noticed. When I tried connecting using mysql client from web server to my DB box its takes time before mysql console shows-up after giving the password. Client and Server are connected through a LAN. Based on ping an average of .1 ms reply.. tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center works with v4.0.23 -- how about V5?
Hi MySQL Query Browser do what you want, but it's less traightforward. Try to click on the wrench/tool just below the data grid, you will be able to update you field (don't forget do click apply changes). That's the only reason why sometimes I still prefer MysqlCC 2005/8/30, Siegfried Heintze [EMAIL PROTECTED]: That is unfortunate that it MSQLCC is deprecated. Is there a gui tool, maybe query-browser, that will let me update, insert and delete without writing SQL statements? That was a very nice feature. Thanks, Siegfried _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 1:12 PM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: Re: MySQL Control Center works with v4.0.23 -- how about V5? Siegfried Heintze [EMAIL PROTECTED] wrote on 08/30/2005 03:11:26 PM: [Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried I think what you are looking for is MySQL Query Browser http://www.mysql.com/products/tools/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question with rows count
Hi, I ran those two queries : mysql select count(id) from forums_data WHERE forums_data.group_id=1 AND forums_data.state=1; +---+ | count(id) | +---+ | 2385 | +---+ 1 row in set (0.11 sec) mysql explain select count(id) from forums_data WHERE forums_data.group_id=1 AND forums_data.state=1; ++-+-+--+---+---+-+-+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+---+-+-+--+---+ | 1 | SIMPLE | forums_data | ref | forum_id3 | forum_id3 | 2 | const,const | 2265 | | ++-+-+--+---+---+-+-+--+---+ 1 row in set (0.00 sec) CREATE TABLE `forums_data` ( `id` int(10) NOT NULL auto_increment, `group_id` tinyint(3) unsigned NOT NULL default '1', `subject` varchar(255) NOT NULL default '', `body` text, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `state` tinyint(1) NOT NULL default '1', PRIMARY KEY (`id`), KEY `forum_id3` (`state`,`group_id`) ); I don't understand why the number rows analyzed returned by EXPLAIN does not match the count(*) of the query. I can understand when it's higher, but lower ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question with rows count
2005/8/28, Michael Stassen [EMAIL PROTECTED]: Pooly wrote: Hi, I ran those two queries : mysql select count(id) from forums_data WHERE forums_data.group_id=1 AND forums_data.state=1; +---+ | count(id) | +---+ | 2385 | +---+ 1 row in set (0.11 sec) mysql explain select count(id) from forums_data WHERE forums_data.group_id=1 AND forums_data.state=1; ++-+-+--+---+---+-+-+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+---+-+-+--+---+ | 1 | SIMPLE | forums_data | ref | forum_id3 | forum_id3 | 2 | const,const | 2265 | | ++-+-+--+---+---+-+-+--+---+ 1 row in set (0.00 sec) CREATE TABLE `forums_data` ( `id` int(10) NOT NULL auto_increment, `group_id` tinyint(3) unsigned NOT NULL default '1', `subject` varchar(255) NOT NULL default '', `body` text, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `state` tinyint(1) NOT NULL default '1', PRIMARY KEY (`id`), KEY `forum_id3` (`state`,`group_id`) ); I don't understand why the number rows analyzed returned by EXPLAIN does not match the count(*) of the query. I can understand when it's higher, but lower ? I'm not sure why you think higher vs. lower makes a difference. To quote from the manual, The rows column indicates the number of rows MySQL believes it must examine to execute the query http://dev.mysql.com/doc/mysql/en/explain.html. Note the word believes. Your table stats may be out of sync. If this is a MyISAM table, you probably just need to run ANALYZE TABLE forums_data; I ran OPTIMIZE TABLE forums_data juste before performing those queries. So, the rows returned int the EXPLAIN command juste indicate a rough count of how many rows MySQL think it will have to examine for running the query ? Ok, it's a bit clearer. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
Hi! Thanks for all your answers. I'll go for a flexible solution, with one column for day, one for month and if i want to compute the age, a year column. This will also give more flexibility if someone does not want to give his birthdate with precision (He would be able to only give the month, and if he give the year I'll compute his age). Thanks! 2005/8/25, Jigal van Hemert [EMAIL PROTECTED]: Pooly wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). For such an application I've used a single integer column to store a number consisting of the month and day (day as 2 digits!!!) concatenated. So dates range from 101 to 1231. The range isn't continuous, but at least the dates are ordered correctly. In this situation you can also easily query ranges (who's celebrating their birthday in the next week/month/etc.). The only caveat is when the start and end of the range is in two different years; then you'll have to split the range up and use a range for each year. Regards, Jigal. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MinGW and MySQL
Hi, MinGW is the GCC compiler for Win32 : http://www.mingw.org/ I get the mysql API working using the comments on this bug : http://bugs.mysql.com/bug.php?id=8059 2005/8/23, Michael Monashev [EMAIL PROTECTED]: Hello P I'm using QT4.0 which works with MinGW. What is the MinGW ? Database server? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UK Bank Holidays
Hi, I guess you have to maintain a table of bank holidays separately, that's the common setup. I don't know any application that can do it (even Excel don't do IIRC). Moreover, bank holidays can depend on your bussiness... 2005/8/25, Shaun [EMAIL PROTECTED]: Hi, I need to work out number of business days worked by staff in our company i.e. Available days = Days in year - (Saturdays + Sundays + Bank Holidays) (Available Days - Time Off) = Capacity Is MySQL aware of UK Bank Holidays or do I have to create a separate table and keep it updated with Bank Holiday dates? 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] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create serial number by select
Hi, Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán [EMAIL PROTECTED]: Hello All! I have a little problem. I can't do serial number in result of select. Example: TABLE1 value -- res1 res2 res3 SELECT (??), value FROM table1 ... 1res1 2res2 3 ... . . I can't build serial number in table1! Thans! Best Regards! Zoli -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LATEST N RECORDS from a table without date field
If you don't order your data but specified a LIMIT clause, what are the records returned ? 24 Aug 2005 17:26:27 +0200, Harald Fuchs [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Praveen KS [EMAIL PROTECTED] writes: Can anyone help with a query to retrieve latest N records. No auto_increment field. No date field. Primary key exists and is populated with random unique values. This means that the only possible definition for latest is highest slno. Translated to SQL: SELECT slno, name FROM tbl ORDER BY slno DESC LIMIT N -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Birthday strategy
Hi, I would like to display a list of members who have their birthday a given day (today for instance). My idea is to store their birth date in a column, and then query the table against the column. But the query would be like : select id from members where MONTH(birthday) = MONTH(NOW()) AND DAY(birthday)=DAY(NOW()) but it would perform a entire table scan with that. What would be your best strategy for that sort of query ? And how would you deal with 29th of february ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
2005/8/24, Cummings, Shawn (GNAPs) [EMAIL PROTECTED]: The 29th wouldn't be an issue because if that is their birthday -- and today is 2/29 -- it will show up. It will only happen once every 4 years... I'll go with the two fields solution and make a special case for leap years. thanks for your help. Pooly wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). My idea is to store their birth date in a column, and then query the table against the column. But the query would be like : select id from members where MONTH(birthday) = MONTH(NOW()) AND DAY(birthday)=DAY(NOW()) but it would perform a entire table scan with that. What would be your best strategy for that sort of query ? And how would you deal with 29th of february ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MinGW and MySQL
Hello, I'm using QT4.0 which works with MinGW. Now I want to use the MySQL API, but it seems to be only working with MS VC++. Should I recompile my own ? Is there any official MinGw libmysqlclient.dll ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from MySQL 4.0 to 4.1
AFAIK, to move the data from one InnoDB file to one table per file, the only option is to dump and restore. (you may want to remove index, and foreign key checks before the restore, and put it back afterwards) 2005/8/23, Rafal Kedziorski [EMAIL PROTECTED]: Hi, we have an J2EE application which ist using MySQL 4.0. There is an bug, which was fixed in MySQL 4.1. We are using tracactions and InnoDB is don't use query cache. Now we have to migrate our DB to MySQL 4.1 for use this feature. In our actual installation we store our data in one inndodb file. After migration we wan't use file per table. What is the best and fastest way to make migration? Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from MySQL 4.0 to 4.1
Hi, why not using the -e otion to mysqldump ? it make an INSERT command as long as your max_command_packet permit it. 2005/8/23, Bruce Dembecki [EMAIL PROTECTED]: Once you decide to use mysqldump, be aware that the quickest way to export/import large files is to use the --tab feature on export and mysqlimport to load the data... Essentially: On the old (4.0) server: mysqldump --tab=/var/tmp/directory mydatabase On the new (4.1) server (assuming you have a new empty mysql data directory with just your MyISAM based mysql database to ensure your permissions files are there): mysql -e create database mydatabase; cat /var/tmp/directory/*.sql | mysql mydatabase mysqlimport mydatabase /var/tmp/directory/*.txt Essentially you are creating a text .sql file for each table with the create table command, and a .txt file with the raw data in tab delimitted format... mysqlimport imports the whole data file as one SQL command, using traditional mysqldump you get a unique SQL insert command for each line of data... doing it once means only writing the indexes etc. once and other time saving advantages... it's far quicker to insert many rows of data as a single INSERT command, than it is to do it row by row. So if you have a large data set and you are doing the export/import thing, that is the way to go... That said there is another option... in theory you can upgrade to 4.1 keeping your shared table files, then tell each table to ALTER TABLE engine=innodb, this will force it to rewrite the table from scratch, and if you have innodb_file_per_table set, it will be created accordingly... The benefit here is your downtime is minimal but the problem is at the end of the day you are still left with your shared innodb table space, and even though it may be mostly empty, you can't clean it up and make it smaller. Best Regards, Bruce On Aug 23, 2005, at 6:19 AM, Rafal Kedziorski wrote: Hi, we have an J2EE application which ist using MySQL 4.0. There is an bug, which was fixed in MySQL 4.1. We are using tracactions and InnoDB is don't use query cache. Now we have to migrate our DB to MySQL 4.1 for use this feature. In our actual installation we store our data in one inndodb file. After migration we wan't use file per table. What is the best and fastest way to make migration? Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB in mysql ----- performance issue.
Hi, 2005/8/22, Kane Wilson [EMAIL PROTECTED]: Thanx , as i understood , i have to keet the raw data in folders and has to be stored that relavent file path's into mysql database, so can u kindly tel me , how it is possible to get the file path ( absolute ot realtive ) of that certain files which are uploaded in to the database from PHP ? what is the function i have to use ? It depends on your application, but you usually choose yourself the location where to move the uploaded file. Usually uploaded file are stored in /tmp temporally : http://uk.php.net/manual/en/function.move-uploaded-file.php Your application move itself the file (hence know the path) and you insert the path in your DB. so that's meant i dont try to hard code file path in my program , This is not actually mysql question ...sorry for asking such out of list questions. Thanx in advance , Kane. Pooly [EMAIL PROTECTED] wrote: 2005/8/20, Kane Wilson : hi Friends, I have a WAP portal which is running based on mysql database. That's meant that contents has been stored in the data base. wallpapers , themes, ringtones, games ...etc data type as BLOB. upto now now problems with the content retrieving. WAP site hosted in REDHAT Linux BOX. I will leave my question by point form , 1. http://www.zend.com/zend/trick/tricks-sept-2001.php according to info of this link , it says there is performance issue when using BLOB for data storing and retriving in mysql and he has recomended some statndard file sharing / accessing methods to use . for instance samba or NFS - in linux. is this happening in mysql ? does it has a solution in mysql ? what could be the best way to use mysql to store / retrive my contents via the wap site ? I'm using mysql bcause , keeping data in a database is very easy to do a site search , rather than keeping contents in folders . The approach generally used is to store in your DB, information concerning your file (its type : ringtones, themes, wallpaper), its size, keywords and everything you find useful, and then keep the file on disk (and having its path sotred in the DB as well). You move all load from serving the files (ringtones, wallpaper) to the FS which does it very well _and_ can be cached by proxies, browser (less load for the server). So you an still do a site search since you keep all the useful information in your DB, but raw data stay on disk. For example if you need to send a ringtone which is in your DB, the browsersend the request, Apache receive it, load PHP/Perl, which then connect to the DB... If the file is on disk, you remove the last 2 operations which willmakethe whole operation faster. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running select on multiple databases serially.
Hi, I'm not sure of what you are looking for, and what do you know. Did you read this ? http://dev.mysql.com/doc/mysql/en/c.html It could give you some clues on how to do your queries from your library. I've never seen any library able to query several servers at the same time. Usually people query the same server (or cluster). If I'm off-topic, pardon me ;-) 2005/8/22, George Cherian [EMAIL PROTECTED]: On Mon, Aug 22, 2005 at 08:03:08PM +0530, George Cherian wrote: So I am surprised that no one has done this before. I am very new to database - (about last week, is when I started getting into the intricacies), so at present I am confused why there isn't such a solution. I had created a database abstraction layer that mapped rows directly into classes of the same name as that of the table, and even had values that were serialized and all. It works, since I always limit the database query to around 10-70 results, and all the inefficient abstraction is carried out only on a maximum of 70 objects. That is why I need another layer at the bottom, since my top layer is too inefficient to do multiple selects. What I meant is that, using this abstraction layer, I had kept my hands clean from the unweildy syntax of SQL. (There is nothing uglier than a lot of sql queries interspersed within the beautiful flow of C code :-). The only query I know is select * from table. Even 'add', 'update' commands confuse me. So I would appreciate if someone can give me some pointers (any urls/links would be nice) as to how to write a client library with threading. Thanks a lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there an easy way to copy a table between two mysql servers?
If you have a mysqlclient an an access to the command line, you can directly pipe it ! mysqldump -h yourhost.com -u username -ppasswd mydatabase | mysql -h yournewhost.som -u newuser -ppasswd newdatabase that's it ! otherwise, yes dump/export. 2005/8/20, John thegimper [EMAIL PROTECTED]: Is there an easy way to copy a table between two mysql servers? Or do i need to export and then import? I´m using php btw. Thanks, Johhn - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB in mysql ----- performance issue.
2005/8/20, Kane Wilson [EMAIL PROTECTED]: hi Friends, I have a WAP portal which is running based on mysql database. That's meant that contents has been stored in the data base. wallpapers , themes, ringtones, games ...etc data type as BLOB. upto now now problems with the content retrieving. WAP site hosted in REDHAT Linux BOX. I will leave my question by point form , 1. http://www.zend.com/zend/trick/tricks-sept-2001.php according to info of this link , it says there is performance issue when using BLOB for data storing and retriving in mysql and he has recomended some statndard file sharing / accessing methods to use . for instance samba or NFS - in linux. is this happening in mysql ? does it has a solution in mysql ? what could be the best way to use mysql to store / retrive my contents via the wap site ? I'm using mysql bcause , keeping data in a database is very easy to do a site search , rather than keeping contents in folders . The approach generally used is to store in your DB, information concerning your file (its type : ringtones, themes, wallpaper), its size, keywords and everything you find useful, and then keep the file on disk (and having its path sotred in the DB as well). You move all load from serving the files (ringtones, wallpaper) to the FS which does it very well _and_ can be cached by proxies, browser (less load for the server). So you an still do a site search since you keep all the useful information in your DB, but raw data stay on disk. For example if you need to send a ringtone which is in your DB, the browsersend the request, Apache receive it, load PHP/Perl, which then connect to the DB... If the file is on disk, you remove the last 2 operations which willmakethe whole operation faster. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fehler
Hallo, Ich werde in english sprechen, so : You get this error usually, when your connection with the DB juste broke, or it's not working, try to test the return value of mysql_connect, and see what is going on. Or you can have this error when the server is too busy. Tschüss ! 2005/8/18, Scott Noyes [EMAIL PROTECTED]: Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88 I would guess that there is some problem with the syntax of your SQL. If you make it a habit to do queries like this: $sql = SELECT * FROM table; $result = mysql_query($sql) or die(mysql_error() . with query $sql); then the error usually becomes ovious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design problem About application related with cached rows
Hi, Try in in two fold. 1. Get all the ID you have in your remote DB 2. check those localy with the ones you have in cache 3. Get all the info you need after you remove the ID you already have. 2005/8/14, Kostas Karadamoglou [EMAIL PROTECTED]: Hello, I try to create an application for my dissertation that caches rows from an romote database. The application handles a cache database which is identical with the original. The only difference is that it does not have autogenerated fields and referential integrity is omitted. I have designed a caching algorithm specific to the context of my applocation. However, I have a problem that I cannot solve it: I want to send a query to the remote database and then store the result to the cache instance. The cache database might have rows that can be duplicate with some rows of the resultset retrieved from the query. The easy solution is to insert all the rows of the resultset one by one after I check their existence at the cache table. However, this solution impose network latency to the network because useless data is moved on the net. Do you know any efficient way to fetch the exception (the rows that dont exist at the cache instance) of rows from the remote database using sql queries? I tried to use the following kind of query but the database returns an overflow message if the query string is too long. SELECT * FROM Customers WHERE CustomerID NOT IN (01,02, 03, ...); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design problem About application related with cached rows
Always reply to the lists, others may have better ideas, others insights... I may have not understand everything in your previous description. Why would you need an extra software on the remote DB ?? All the steps I've describe should work from the client. All you want is a kind of cache for the client, that you can invalidate when you judge it needed ? If you need to check UPDATE on your remote DB, you're stuck, except with 5.0.10 (at least) with triggers. If it's only INSERT, then you just have to query the last rows you don't have (with a timestamp, an ID). If you don't have any total order relationship in your data,but have a unique ID, retrieve all the ID from your remote DB, remote with the one you have locally, and then query the full rows. No extra software needed on the server ! 2005/8/14, Kostas Karadamoglou [EMAIL PROTECTED]: I thought this solution but it needs an extra software at the remote database. I would prefer to contact from the cache database directly to the remote database using SQL. But even using those 3 steps there is a problem. The only interface that I have is JDBC/SQL. That means that the only solution would be to fetch all the rows in a resultset locally(remote database) and then in a for loop I must check all the IDs that I dont need with the ID of the current row within the loop. I think this is a little bit time consuming!! Do you know I more effective way to do this? thank you in advance, Kostas Pooly wrote: Hi, Try in in two fold. 1. Get all the ID you have in your remote DB 2. check those localy with the ones you have in cache 3. Get all the info you need after you remove the ID you already have. 2005/8/14, Kostas Karadamoglou [EMAIL PROTECTED]: Hello, I try to create an application for my dissertation that caches rows from an romote database. The application handles a cache database which is identical with the original. The only difference is that it does not have autogenerated fields and referential integrity is omitted. I have designed a caching algorithm specific to the context of my applocation. However, I have a problem that I cannot solve it: I want to send a query to the remote database and then store the result to the cache instance. The cache database might have rows that can be duplicate with some rows of the resultset retrieved from the query. The easy solution is to insert all the rows of the resultset one by one after I check their existence at the cache table. However, this solution impose network latency to the network because useless data is moved on the net. Do you know any efficient way to fetch the exception (the rows that dont exist at the cache instance) of rows from the remote database using sql queries? I tried to use the following kind of query but the database returns an overflow message if the query string is too long. SELECT * FROM Customers WHERE CustomerID NOT IN (01,02, 03, ...); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
