Re: 1 Machine with 4 GB RAM for Big Size MySQL Data Size
On Friday 04 September 2009 08:15:35 pm muhammad subair wrote: > On Sat, Sep 5, 2009 at 5:10 AM, mos wrote: > > At 11:48 AM 9/4/2009, you wrote: > >> One of my potential clients want to migrate their application to web > >> based (PHP & MySQL), estimates of the data size is 24GB and growth per > >> month is 20MB of data. Unfortunately, they could only use 1 sever > >> machine with 4GB RAM. > >> > >> The application used in intranet, just running simple transactions and > >> the number of users concurent is under 10. > >> > >> I need information and suggestion about this condition, whether the > >> effort spent on implementation and future maintenance is not too large > >> for use MySQL with this condition? > >> > >> *btw sorry for my English* > >> > >> Thanks you very much, > >> -- > >> Muhammad Subair > > > > Muhammad, > > It will depend on your queries and how efficiently you write them. A > > poorly constructed query on a 24MB table will perform worse than an > > optimized query on a 24GB table. If you can show us your table structure > > and query example, (are you joining tables?), then we can guestimate > > better. > > > > Mike > > > > -- > > Thank you for the feedback and input from all friends. > > Currently I have yet enter the design phase, just survey phase to get the > information about the data which will migrate from the legacy application. > Fyi, the input data which will migrate to MySQL is txt and not normal for > Relational Database. > > Based on existing feedbacks, I conclude that this project makes sense and > can be continued. Perhaps with a note of the problem in vailure single > point because there is only 1 server. > > Furthermore if there is progress again, I'll try sharing. > > Thank you very much > Perhaps its worth looking at a master-slave relationship between 2 servers if you are concerned about a single point of failure. Colin -- There is a 20% chance of tomorrow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 1 Machine with 4 GB RAM for Big Size MySQL Data Size
On Sat, Sep 5, 2009 at 5:10 AM, mos wrote: > At 11:48 AM 9/4/2009, you wrote: > >> One of my potential clients want to migrate their application to web based >> (PHP & MySQL), estimates of the data size is 24GB and growth per month is >> 20MB of data. Unfortunately, they could only use 1 sever machine with 4GB >> RAM. >> >> The application used in intranet, just running simple transactions and the >> number of users concurent is under 10. >> >> I need information and suggestion about this condition, whether the effort >> spent on implementation and future maintenance is not too large for use >> MySQL with this condition? >> >> *btw sorry for my English* >> >> Thanks you very much, >> -- >> Muhammad Subair >> > > Muhammad, > It will depend on your queries and how efficiently you write them. A > poorly constructed query on a 24MB table will perform worse than an > optimized query on a 24GB table. If you can show us your table structure > and query example, (are you joining tables?), then we can guestimate better. > > Mike > > -- > Thank you for the feedback and input from all friends. Currently I have yet enter the design phase, just survey phase to get the information about the data which will migrate from the legacy application. Fyi, the input data which will migrate to MySQL is txt and not normal for Relational Database. Based on existing feedbacks, I conclude that this project makes sense and can be continued. Perhaps with a note of the problem in vailure single point because there is only 1 server. Furthermore if there is progress again, I'll try sharing. Thank you very much -- Muhammad Subair
Re: 1 Machine with 4 GB RAM for Big Size MySQL Data Size
At 11:48 AM 9/4/2009, you wrote: One of my potential clients want to migrate their application to web based (PHP & MySQL), estimates of the data size is 24GB and growth per month is 20MB of data. Unfortunately, they could only use 1 sever machine with 4GB RAM. The application used in intranet, just running simple transactions and the number of users concurent is under 10. I need information and suggestion about this condition, whether the effort spent on implementation and future maintenance is not too large for use MySQL with this condition? *btw sorry for my English* Thanks you very much, -- Muhammad Subair Muhammad, It will depend on your queries and how efficiently you write them. A poorly constructed query on a 24MB table will perform worse than an optimized query on a 24GB table. If you can show us your table structure and query example, (are you joining tables?), then we can guestimate better. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ALTER TABLE order / optimization
If your table testtab is populated, neither suggestion is efficient. You could the following instead: # # Create an empty table `testtab_copy` # 1) CREATE TABLE testtab_copy LIKE testtab; 2) Do either of you suggestions: ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST,ADD COLUMN b_col char(4) FIRST,ADD COLUMN a_col char(4) FIRST; or ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; 3) INSERT INTO testtab_copy (d_col) SELECT d_col FROM testtab; 4) DROP TABLE testtab; 5) ALTER TABLE testtab_copy RENAME testtab; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Friday, September 04, 2009 3:53 PM To: mysql@lists.mysql.com Subject: ALTER TABLE order / optimization Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no "harm" in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: a better way, code technique?
+2 > -Original Message- > From: Brent Baisley [mailto:brentt...@gmail.com] > Sent: Friday, September 04, 2009 6:21 AM > To: AndrewJames > Cc: mysql@lists.mysql.com > Subject: Re: a better way, code technique? > > You should store the current user id in a session variable. Then you > don't have to hit the database at all. > > There really is no short way of doing it. Normally you would create a > function (i.e. runQuery) that you pass the query too. Then it handles > running the query, fetching the data, error checking, etc. That way > you don't have to rewrite the same lines every time you want to run a > query. > > Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ALTER TABLE order / optimization
Given table: CREATE TABLE testtab (d_col CHAR(4)); Question 1: It appears that there is no "harm" in just appending directives onto the alter table command even if the order doesn't make sense. It appears the parser figures it out... For example... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST, ADD COLUMN b_col char(4) FIRST, ADD COLUMN a_col char(4) FIRST; ...does end up with a_col then b_col then c_col then d_col... but does it matter and I doing something wrong? Question 2: Is that any more efficient than doing... ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN b_col char(4) FIRST; ALTER TABLE testtab ADD COLUMN a_col char(4) FIRST; If it's NOT more efficient then I won't bother rewriting this one app which runs slowly to join them up because it certain is easier to read and debug with each modification on its own line. Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Fwd: Help with Timestamp invalid value error
Mysql doesn't store sub-second values. try 2008-03-09 02:56:34 Instead of 2008-03-09 02:56:34.737 Regards, Gavin Towey -Original Message- From: Proemial [mailto:proem...@gmail.com] Sent: Friday, September 04, 2009 8:37 AM To: John Daisley Cc: mysql@lists.mysql.com Subject: Re: Fwd: Help with Timestamp invalid value error I had already tried that, actually. Produces the same error. I should have mentioned that as well, sorry! The version is 5.1.34 thanks for the help, btw! Martin On Fri, Sep 4, 2009 at 11:14 AM, John Daisley wrote: > Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' > be causing the problem. > > Try inserting the value as > > '2008-03-09 02:56:34.737' > > Do you get the same error? > > What mysql version is your server? > > > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Proemial > Sent: Friday, September 04, 2009 3:39 PM > To: mysql@lists.mysql.com > Subject: Fwd: Help with Timestamp invalid value error > > Currently set to: > NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > On Fri, Sep 4, 2009 at 10:24 AM, John > Daisley wrote: >> What is your sql_mode set to? >> >> I tried inserting that value into a timestamp column on our test server and >> it works fine. >> >> Regards >> >> John Daisley >> Mobile +44(0)7812 451238 >> Email j...@butterflysystems.co.uk >> >> Certified MySQL 5 Database Administrator (CMDBA) >> Certified MySQL 5 Developer >> Cognos BI Developer >> >> --- >> Sent from HP IPAQ mobile device. >> >> >> > > > [The entire original message is not included] > -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: a better way, code technique?
You could mash it into two lines. Though I think the verbose syntax is more
readable.
mysql_fetch_array( mysql_query("SELECT uid FROM users WHERE
users.username='".mysql_real_escape_string($username)."'") );
$u = $uid['uid'];
However do you really think that 4 lines is too much to make a (possible)
network call to an external resource, ask it to parse a statement and retrieve
a specific piece of data, then return it to you and assign it to a variable?
For one, that's what functions are for, write it once then call your function!
Two, you should look at how much code is already hidden from you in those few
functions! =P
Regards,
Gavin Towey
-Original Message-
From: Brent Baisley [mailto:brentt...@gmail.com]
Sent: Friday, September 04, 2009 6:21 AM
To: AndrewJames
Cc: mysql@lists.mysql.com
Subject: Re: a better way, code technique?
You should store the current user id in a session variable. Then you
don't have to hit the database at all.
There really is no short way of doing it. Normally you would create a
function (i.e. runQuery) that you pass the query too. Then it handles
running the query, fetching the data, error checking, etc. That way
you don't have to rewrite the same lines every time you want to run a
query.
Brent Baisley
On Fri, Sep 4, 2009 at 6:51 AM, AndrewJames wrote:
> is there a better way (hopefully simpler) to code this?
>
> i want to get the user id of the logged in user to use in my next statement.
>
> $q1 = sprintf("SELECT uid FROM users WHERE users.username='$username'");
> $result1 = mysql_query($q1);
> $uid = mysql_fetch_array($result1);
> $u = $uid['uid'];
>
> it seems like a long way around to get 1 bit of data??
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC OS 10.6 Snow Leopard Breaks MySQL
I don't appear to have a /etc/my.cnf file - could the upgrade have removed it? Here is the error string I get when I try and start MySQL: finhagens-macbook-pro:bin finhagen$ ./mysqld & [1] 4697 finhagens-macbook-pro:bin finhagen$ 090904 10:25:18 [ERROR] Can't find messagefile '/usr/local/mysql/share/english/errmsg.sys' 090904 10:25:18 [Warning] Can't create test file /usr/local/mysql/data/finhagens-macbook-pro.lower-test 090904 10:25:18 [Warning] Can't create test file /usr/local/mysql/data/finhagens-macbook-pro.lower-test ./mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 2) 090904 10:25:18 [ERROR] Aborting I have noted that a new path was created for mysql during the upgrade: /usr/local/mysql-5.1.31-osx10.5-86/ ...bin ...share ...data etc. Obviously that new path is resulting in the errors above. -What's the best way to inform MySQL the direct structure changed? -Is there a 5.4 version for MAC OS 10.6? -Would it make sense to reinstall with that version? (my data base is tiny and its relatively easy to export and import it and I am the only user) Hagen -Original Message- Sent: Friday, September 04, 2009 11:03 AM To: Hagen Subject: RE: MAC OS 10.6 Snow Leopard Breaks MySQL What is the connect string in the /etc/my.cnf file? -Original Message- From: Hagen [mailto:finha...@comcast.net] Sent: Friday, September 04, 2009 9:45 AM To: mysql@lists.mysql.com Subject: MAC OS 10.6 Snow Leopard Breaks MySQL I upgraded to MAC OS 10.6 Snow Leopard over the weekend and now I find that upgrade appears to have broken MySQL (5.1.31 MySQL Community Server (GPL)). When I attempt to start MySQL I get: ERROR 2002: (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock (2) Has anyone else had this issue and do you know of a fix or work around? Hagen Finley Boulder, CO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jgor...@westernwats.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 1 Machine with 4 GB RAM for Big Size MySQL Data Size
The size of the data is largely irrelevant, it depends on how much of it you need to use at once. For most setups, 4GB should be more than enough. A single server is always a bad idea since it's a single point of failure. Concurrent users isn't really relevant with the database either since it's unlikely all of them will be running a query at the same time. Unless your queries are really slow. I'm using a server with 2GB RAM that gets 300K hits a day, adds 250K records per day to the databases, with one table having almost 50 million records. Brent Baisley On Fri, Sep 4, 2009 at 12:48 PM, muhammad subair wrote: > One of my potential clients want to migrate their application to web based > (PHP & MySQL), estimates of the data size is 24GB and growth per month is > 20MB of data. Unfortunately, they could only use 1 sever machine with 4GB > RAM. > > The application used in intranet, just running simple transactions and the > number of users concurent is under 10. > > I need information and suggestion about this condition, whether the effort > spent on implementation and future maintenance is not too large for use > MySQL with this condition? > > *btw sorry for my English* > > Thanks you very much, > -- > Muhammad Subair > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Database design - help
> given the following table layouts > URLs: > URL_ID (primary key for URL) > URL_TEXT > > URL_CATEGORY > URL_ID (key which points to URL.URL_ID) > CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) > SUBCATEGORY_ID > PK: (URL_ID, CATEGORY_ID) > CATEGORY > CATEGORY_ID (primary Key for Category) > CATEGORY_TEXT > > SUBCAT > SUBCAT_ID(concatenated key for SubCat) > CATEGORY_ID (concatenated key for Subcat) > SUBCAT_TEXT > so the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE)URL_ID1->1 URL.URL_ID CATEGORY.CATEGORY_ID1<---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > From: bobsh...@ntlworld.com > To: mysql@lists.mysql.com > CC: john.l.me...@gmail.com > Subject: Re: Database design - help > Date: Fri, 4 Sep 2009 16:24:22 +0100 > > Hi > > Thanks for all the responses. However I am still stuck for a MySQL db I > can create > and code in PHP. Attached is a brief example of data to be used. > > One problem I have is with providing a listing that includes ... > WTBC (Category without SubCats) and the 3 Zones (also, Cats without > SubCats ??? ) > (This is for a complete WTBC listing, in practice it may list depending on > selected Zone) > > > The example Schema is interesting, but is there another way of storing all > links > in one table and join them to Category and SubCat tables ? > An example of the ER Diagram would also be helpful to me. > > > cheers > > > > > > - Original Message - > From: "John Meyer" > To: "BobSharp" > Cc: > Sent: Monday, August 31, 2009 4:56 PM > Subject: Re: Database design - help > > > > BobSharp wrote: > >> As a complete newbie in MySQL, I need a database > >> to store URLs related to Tenpin Bowling. > >> > >> There are several Categories ... Equipment Manufacturers, > >> Organistations, (UK) ProShops, (UK) Bowling Centres, Personal > >> Websites, Misc., Coaching & Instructional websites, etc. > >> > >> There will be some sub-categories. > >> eg: Organistions will have ... Zones of WTBC, National Organisations > >> within > >> the Zones, UK organisations, Disabled Bowling organisations, ... > >> eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, > >> etc. > >> > >> Can anyone suggest how I should set out tables for this database ? > > > > > > Here's one suggestion > > > > Table: > > > > URLs: > > URL_ID > > URL_TEXT > > > > CATEGORY > > CATEGORY_ID > > CATEGORY_TEXT > > > > SUBCAT > > SUBCAT_ID > > CATEGORY_ID > > SUBCAT_TEXT > > > > URL_CATEGORY > > URL_ID > > CATEGORY_ID > > SUBCATEGORY_ID > > PK: (URL_ID, CATEGORY_ID) > > > > > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 > 05:50:00 > > > -- > I am using the free version of SPAMfighter. > We are a community of 6 million users fighting spam. > SPAMfighter has removed 13901 of my spam emails to date. > Get the free SPAMfighter here: http://www.spamfighter.com/len > > The Professional version does not have this message > _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009
Re: 1 Machine with 4 GB RAM for Big Size MySQL Data Size
What sort of data? Is it currently stored in a database? If so, how many tables? 24GB of text data in a single table is quite a bit, but manageable if maintained properly. 24 GB of binary data on the other hand, is not very much at all. Colin On Friday 04 September 2009 12:48:18 pm muhammad subair wrote: > One of my potential clients want to migrate their application to web based > (PHP & MySQL), estimates of the data size is 24GB and growth per month is > 20MB of data. Unfortunately, they could only use 1 sever machine with 4GB > RAM. > > The application used in intranet, just running simple transactions and the > number of users concurent is under 10. > > I need information and suggestion about this condition, whether the effort > spent on implementation and future maintenance is not too large for use > MySQL with this condition? > > *btw sorry for my English* > > Thanks you very much, > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
1 Machine with 4 GB RAM for Big Size MySQL Data Size
One of my potential clients want to migrate their application to web based (PHP & MySQL), estimates of the data size is 24GB and growth per month is 20MB of data. Unfortunately, they could only use 1 sever machine with 4GB RAM. The application used in intranet, just running simple transactions and the number of users concurent is under 10. I need information and suggestion about this condition, whether the effort spent on implementation and future maintenance is not too large for use MySQL with this condition? *btw sorry for my English* Thanks you very much, -- Muhammad Subair
MAC OS 10.6 Snow Leopard Breaks MySQL
I upgraded to MAC OS 10.6 Snow Leopard over the weekend and now I find that upgrade appears to have broken MySQL (5.1.31 MySQL Community Server (GPL)). When I attempt to start MySQL I get: ERROR 2002: (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock (2) Has anyone else had this issue and do you know of a fix or work around? Hagen Finley Boulder, CO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: Help with Timestamp invalid value error
I had already tried that, actually. Produces the same error. I should have mentioned that as well, sorry! The version is 5.1.34 thanks for the help, btw! Martin On Fri, Sep 4, 2009 at 11:14 AM, John Daisley wrote: > Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' > be causing the problem. > > Try inserting the value as > > '2008-03-09 02:56:34.737' > > Do you get the same error? > > What mysql version is your server? > > > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Proemial > Sent: Friday, September 04, 2009 3:39 PM > To: mysql@lists.mysql.com > Subject: Fwd: Help with Timestamp invalid value error > > Currently set to: > NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > On Fri, Sep 4, 2009 at 10:24 AM, John > Daisley wrote: >> What is your sql_mode set to? >> >> I tried inserting that value into a timestamp column on our test server and >> it works fine. >> >> Regards >> >> John Daisley >> Mobile +44(0)7812 451238 >> Email j...@butterflysystems.co.uk >> >> Certified MySQL 5 Database Administrator (CMDBA) >> Certified MySQL 5 Developer >> Cognos BI Developer >> >> --- >> Sent from HP IPAQ mobile device. >> >> >> > > > [The entire original message is not included] > -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Release?
Hi, I noticed that the 6.11 branch is not available for download anymore. Did they rename it to 5.4? Thanks, -Alex
Re: Database design - help
Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: "John Meyer" To: "BobSharp" Cc: Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching & Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Fwd: Help with Timestamp invalid value error
Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' be causing the problem. Try inserting the value as '2008-03-09 02:56:34.737' Do you get the same error? What mysql version is your server? Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Proemial Sent: Friday, September 04, 2009 3:39 PM To: mysql@lists.mysql.com Subject: Fwd: Help with Timestamp invalid value error Currently set to: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION On Fri, Sep 4, 2009 at 10:24 AM, John Daisley wrote: > What is your sql_mode set to? > > I tried inserting that value into a timestamp column on our test server and > it works fine. > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Help with Timestamp invalid value error
Currently set to: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION On Fri, Sep 4, 2009 at 10:24 AM, John Daisley wrote: > What is your sql_mode set to? > > I tried inserting that value into a timestamp column on our test server and > it works fine. > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Proemial > Sent: Friday, September 04, 2009 2:27 PM > To: mysql@lists.mysql.com > Subject: Help with Timestamp invalid value error > > I run a process which loads a series of timestamped data into a table. > I use the TIMESTAMP column. > > I have a single value with a timestamp of '2008-03-9 2:56:34.737' > which fails on insert with 'incorrect datetime'. Days before, and > after work. Hours later in the day work. I thought it might be > related to the timezone, but have none of the TZ tables populated. > > Can someone tell me why this date in particular fails? > > The query that fails: > INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID, > topic_list, product_list) > VALUES ( > '2008-03-9 2:56:34.737', > '', > '', > '') > > Table: > newsID int(10) unsigned PRI auto_increment > timeStamp timestamp CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP > TRStoryID varchar(128) > topic_list varchar(512) > product_list varchar(512) > > > > [The entire original message is not included] > -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help with Timestamp invalid value error
What is your sql_mode set to? I tried inserting that value into a timestamp column on our test server and it works fine. Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Proemial Sent: Friday, September 04, 2009 2:27 PM To: mysql@lists.mysql.com Subject: Help with Timestamp invalid value error I run a process which loads a series of timestamped data into a table. I use the TIMESTAMP column. I have a single value with a timestamp of '2008-03-9 2:56:34.737' which fails on insert with 'incorrect datetime'. Days before, and after work. Hours later in the day work. I thought it might be related to the timezone, but have none of the TZ tables populated. Can someone tell me why this date in particular fails? The query that fails: INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID, topic_list, product_list) VALUES ( '2008-03-9 2:56:34.737', '', '', '') Table: newsID int(10) unsignedPRI auto_increment timeStamp timestamp CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP TRStoryID varchar(128) topic_list varchar(512) product_listvarchar(512) [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with Timestamp invalid value error
I run a process which loads a series of timestamped data into a table. I use the TIMESTAMP column. I have a single value with a timestamp of '2008-03-9 2:56:34.737' which fails on insert with 'incorrect datetime'. Days before, and after work. Hours later in the day work. I thought it might be related to the timezone, but have none of the TZ tables populated. Can someone tell me why this date in particular fails? The query that fails: INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID, topic_list, product_list) VALUES ( '2008-03-9 2:56:34.737', '', '', '') Table: newsID int(10) unsignedPRI auto_increment timeStamp timestamp CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP TRStoryID varchar(128) topic_list varchar(512) product_listvarchar(512) Thank you! Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a better way, code technique?
You should store the current user id in a session variable. Then you
don't have to hit the database at all.
There really is no short way of doing it. Normally you would create a
function (i.e. runQuery) that you pass the query too. Then it handles
running the query, fetching the data, error checking, etc. That way
you don't have to rewrite the same lines every time you want to run a
query.
Brent Baisley
On Fri, Sep 4, 2009 at 6:51 AM, AndrewJames wrote:
> is there a better way (hopefully simpler) to code this?
>
> i want to get the user id of the logged in user to use in my next statement.
>
> $q1 = sprintf("SELECT uid FROM users WHERE users.username='$username'");
> $result1 = mysql_query($q1);
> $uid = mysql_fetch_array($result1);
> $u = $uid['uid'];
>
> it seems like a long way around to get 1 bit of data??
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to get the time of the client in sleep status
mysqladmin processlist -v or mysql> show processlist; Cheers Claudio 2009/9/4 stutiredboy > hi,all > > can i get how long the client(s) in sleep staus after the client > connected to mysql server > > how can i do it ? > > thanks very much > > tiredboy > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- Claudio
AW: AW: Re: a better way, code technique?
>-Ursprüngliche Nachricht- >Von: AndrewJames [mailto:andrewhu...@gmail.com] >Gesendet: Freitag, 4. September 2009 13:35 >An: Skoric, Majk; p...@computer.org; mysql@lists.mysql.com >Betreff: Re: AW: Re: a better way, code technique? > >hahah thank-you, love the responses here. you guys are awesome.. > >ps, where does the %s come from? Read -> http://de2.php.net/sprintf Majk
Re: AW: Re: a better way, code technique?
hahah thank-you, love the responses here. you guys are awesome..
ps, where does the %s come from?
--
From:
Sent: Friday, September 04, 2009 9:11 PM
To: ;
Subject: AW: Re: a better way, code technique?
-Ursprüngliche Nachricht-
Von: Per Jessen [mailto:p...@computer.org]
Gesendet: Freitag, 4. September 2009 13:05
An: mysql@lists.mysql.com
Betreff: Re: a better way, code technique?
AndrewJames wrote:
is there a better way (hopefully simpler) to code this?
i want to get the user id of the logged in user to use in my next
statement.
$q1 = sprintf("SELECT uid FROM users WHERE
users.username='$username'");
The only "improvement" I can see is:
$q1 = sprintf("SELECT uid FROM users WHERE users.username='%s'",
$username);
sprintf only adds overhead to this. There is no need to use it here.
You can just use $q = "SELECT ...";
Or if you wanna have it more readable use heredoc style
$q = <<
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: Re: a better way, code technique?
-Ursprüngliche Nachricht-
Von: Per Jessen [mailto:p...@computer.org]
Gesendet: Freitag, 4. September 2009 13:05
An: mysql@lists.mysql.com
Betreff: Re: a better way, code technique?
AndrewJames wrote:
>> is there a better way (hopefully simpler) to code this?
>>
>> i want to get the user id of the logged in user to use in my next
>> statement.
>>
>> $q1 = sprintf("SELECT uid FROM users WHERE
>> users.username='$username'");
>The only "improvement" I can see is:
>
>$q1 = sprintf("SELECT uid FROM users WHERE users.username='%s'",
>$username);
sprintf only adds overhead to this. There is no need to use it here.
You can just use $q = "SELECT ...";
Or if you wanna have it more readable use heredoc style
$q = <<
Re: a better way, code technique?
AndrewJames wrote:
> is there a better way (hopefully simpler) to code this?
>
> i want to get the user id of the logged in user to use in my next
> statement.
>
> $q1 = sprintf("SELECT uid FROM users WHERE
> users.username='$username'");
The only "improvement" I can see is:
$q1 = sprintf("SELECT uid FROM users WHERE users.username='%s'",
$username);
/Per Jessen, Zürich
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: a better way, code technique?
little error
-list($id) = mysql_fetch_row(mysql_query("SELECT uid FROM users WHERE
+list($uid) = mysql_fetch_row(mysql_query("SELECT uid FROM users WHERE
Majk
-Ursprüngliche Nachricht-
Von: majk.sko...@eventim.de [mailto:majk.sko...@eventim.de]
Gesendet: Freitag, 4. September 2009 13:00
An: andrewhu...@gmail.com; mysql@lists.mysql.com
Betreff: AW: a better way, code technique?
You should escape $username before passing it to mysql if its user
submitted data ... sql-injection
one/two liner: but error prone!
$un = mysql_real_escape_string($username);
list($id) = mysql_fetch_row(mysql_query("SELECT uid FROM users WHERE
username='{$un}'");
better would be
$result = mysql_query...
if (!$result)
die("error: ".mysql_error());
list($uid) = mysql_fetch_row($result);
if (!$uid)
die("no user with {$uname} found!");
do something with $uid
Majk
-Ursprüngliche Nachricht-
Von: AndrewJames [mailto:andrewhu...@gmail.com]
Gesendet: Freitag, 4. September 2009 12:52
An: mysql@lists.mysql.com
Betreff: a better way, code technique?
is there a better way (hopefully simpler) to code this?
i want to get the user id of the logged in user to use in my next statement.
$q1 = sprintf("SELECT uid FROM users WHERE users.username='$username'");
$result1 = mysql_query($q1);
$uid = mysql_fetch_array($result1);
$u = $uid['uid'];
it seems like a long way around to get 1 bit of data??
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=majk.sko...@eventim.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=majk.sko...@eventim.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: a better way, code technique?
You should escape $username before passing it to mysql if its user
submitted data ... sql-injection
one/two liner: but error prone!
$un = mysql_real_escape_string($username);
list($id) = mysql_fetch_row(mysql_query("SELECT uid FROM users WHERE
username='{$un}'");
better would be
$result = mysql_query...
if (!$result)
die("error: ".mysql_error());
list($uid) = mysql_fetch_row($result);
if (!$uid)
die("no user with {$uname} found!");
do something with $uid
Majk
-Ursprüngliche Nachricht-
Von: AndrewJames [mailto:andrewhu...@gmail.com]
Gesendet: Freitag, 4. September 2009 12:52
An: mysql@lists.mysql.com
Betreff: a better way, code technique?
is there a better way (hopefully simpler) to code this?
i want to get the user id of the logged in user to use in my next statement.
$q1 = sprintf("SELECT uid FROM users WHERE users.username='$username'");
$result1 = mysql_query($q1);
$uid = mysql_fetch_array($result1);
$u = $uid['uid'];
it seems like a long way around to get 1 bit of data??
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=majk.sko...@eventim.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
a better way, code technique?
is there a better way (hopefully simpler) to code this?
i want to get the user id of the logged in user to use in my next statement.
$q1 = sprintf("SELECT uid FROM users WHERE users.username='$username'");
$result1 = mysql_query($q1);
$uid = mysql_fetch_array($result1);
$u = $uid['uid'];
it seems like a long way around to get 1 bit of data??
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to get the time of the client in sleep status
hi,all can i get how long the client(s) in sleep staus after the client connected to mysql server how can i do it ? thanks very much tiredboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
Many thanks for your query, seems we need to group it like Wolfgang's
does.
Willy
On Thu, 2009-09-03 at 22:33 -0700, Manasi Save wrote:
> may be you can use IN clause:
>
> SELECT SUM(price)*0.5 AS price1, SUM(price)*0.65 AS price2 FROM table
> WHERE partner IN ('A', 'B');
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
Many thanks for the query. It works ;)
Willy
On Fri, 2009-09-04 at 08:09 +0200, Wolfgang Schaefer wrote:
> sangprabv wrote:
> > I have these query:
> > SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A';
> > SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B';
> > Is it possible to make the queries into 1 single query? How to make it
> > happen? Many thanks for helps.
> >
> >
> >
> > Willy
> >
> >
> >
> You can group by partners and then calculate the price for the certain
> partner, if that is what you want.
>
> SELECT partner, IF(partner = 'A', sum(price)*0.5, '-') as price1,
> IF(partner = 'B', sum(price)*0.65, '-')
> as price2
> FROM table
> WHERE partner IN ('A', 'B')
> GROUP BY partner;
>
> cheers,
> wolfgang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
