Re: Mystery error in GRANT statement

[Richard]

> Date: Monday, October 03, 2016 23:18:14 -0700
> From: James Moe <ji...@sohnen-moe.com>
>
> On 10/03/2016 08:16 PM, Richard wrote:
>> If you want/need to use it I believe you need to use the
>> "backtick" to quote the name
>> 
>   Yes, that worked. Thank you.
>   Is there an easy way to rename a database?


See the documentation for the syntax:

  <http://dev.mysql.com/doc/refman/5.7/en/alter-database.html>

  <http://dev.mysql.com/doc/refman/5.7/en/rename-table.html>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mystery error in GRANT statement

[Richard]

> Date: Monday, October 03, 2016 18:39:22 -0700
> From: James Moe 
>
> opensuse v42.1
> linux 4.1.31-30-default x86_64
> 10.0.26-MariaDB
> 
> I have a database named "sma-v4-01". The GRANT statement does not
> like that database name:
> 
> MariaDB [sma-v4-01]> GRANT ALL ON 'sma-v4-01'.* TO
> ''@'sma-station14l' IDENTIFIED BY 'xx';
> 
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MariaDB server version for the right
> syntax to use near ''sma-v4-01'.* TO 'x'@'sma-station14l'
> IDENTIFIED BY 'xx'' at line 1
> 
> If I replace 'sma-v4-01' with 'sma_joomla', the statement is
> accepted.
> 
> What is wrong with 'sma-v4-01'?


The "dash" (-) isn't really a permissible table name character. If
you want/need to use it I believe you need to use the "backtick" to
quote the name [unless you set "ansi_quotes" in which case you can
use the double quote character].



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: dump then truncate - in between anything?

[Richard]

> Date: Wednesday, March 09, 2016 14:38:45 +
> From: lejeczek 
>
> hi everybody
> 
> I imagine this is theoretical rather than practical question,
> albeit I don't have much practice, so I hope experts could comment
> logical view of the procedure is: mysqldump && truncate - what are
> the chances that something gets in between and I loose it?
> 
> many thanks
> L.

If what you are trying to do is something like:

   - archive contents of live table from current period
   - empty live table
   - start collecting input again

you might want to look at the "rename" capability. Doing something
like:
  
rename  -> 
rename  -> 

will be much faster than trying to dump and empty a live table. With
"rename" you'll have your data from the last period in "" so can
handle it at will.

You'll want to look at what a rename vs. truncate vs. delete do to
make certain there isn't something with one of them that might cause
you a problem.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

When to create a new database

[Richard Reina]

If I were keeping tract of high school sports statistics and thus designed
the following tables:

sports, rules, statistical definitions

and

players, teams, games

Would it be a good or bad idea to put the first set of tables in a separate
database called "library" since they are used for reference and rarely
change? What would be the pros and cons of using two different databases?

Thanks

How to get auto Increment ID of INSERT?

[Richard Reina]

If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
record that you have just inserted?

Thanks

Relational query question

[Richard Reina]

If I have three simple tables:

mysql> select * from customer;
+++
| ID | NAME   |
+++
|  1 | Joey   |
|  2 | Mike   |
|  3 | Kellie |
+++
3 rows in set (0.00 sec)

mysql> select * from fruit;
++-+
| ID | NAME|
++-+
|  1 | Apples  |
|  2 | Grapes  |
|  3 | Oranges |
|  4 | Kiwis   |
++-+
4 rows in set (0.00 sec)

mysql> select * from purchases;
++-+--+
| ID | CUST_ID | FRUIT_ID |
++-+--+
|  2 |  3 |   2   |
|  3 |  1 |   4   |
|  4 |  1 |   2   |
|  5 |  2 |   1   |
++-+--+

I am having trouble understanding a relational query. How can I select
those fruits that Joey has not purchased?

Re: Trouble with LEFT JOIN

[Richard Reina]

Peter,

Thank you very much for your reply. Three weeks later I am realizing that
the 'NOT IN' solution I had above does not work after all. Accordingly, I
went back and looked at your solution and it is close but it only gives
NULL results I am looking for something that excludes challenges that have
are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying.

This may be unsolvable.

2015-09-04 12:30 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:

> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:
>
>> On 2015-09-04 9:40 AM, Richard Reina wrote:
>>
>>> I have the following two tables;
>>>
>>> mysql> select * from challenge;
>>> +++-++--+-+--+
>>> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
>>> +++-++--+-+--+
>>> |  1 |   1 |  1 | Fall  | 2015 |
>>>  1175
>>> |
>>> |  2 |   1 |  4 | Fall  | 2015 |
>>>  1175
>>> |
>>> |  3 |   1 |  3 | Fall  | 2015 |
>>>  1175
>>> |
>>> |  4 |   1 |10 | Fall  | 2015 |
>>>  1175 |
>>> |  5 |   1 |13 | Fall  | 2015 |
>>>  1175 |
>>> |  6 |   1 |  2 | Fall  | 2015 |
>>>  1175
>>> |
>>> ++--+--++---+-+
>>> 6 rows in set (0.00 sec)
>>>
>>> mysql> select * from patrocinio;
>>> +++---+-+
>>> | ID | PTRN_ID | CHLNG_ID | AMOUNT |
>>> +++---+-+
>>> |  1 | 1 | 1 |   1.00  |
>>> |  2 | 4 | 3 |   2.00  |
>>> |  3 | 3 | 6 |   1.00  |
>>> ++---+-++
>>> I would like to select all rows from challenges which are NOT linked to a
>>> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
>>> challenges.
>>>
>>> I am trying to go about this with a LEFT JOIN query but it does not seem
>>> to
>>> be working for me.
>>>
>>> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
>>> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;
>>>
>>
>> ... where p.chlng_id IS NULL;
>>
>> (Look up exclusion joins)
>>
>> PB
>>
>> -
>>
>>
>
> Hi Peter,
>
> Thanks for the reply. Along those lines I have also tried:
>
> select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE
> p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL;
>
> But that's not working either.
>
>
> drop table if exists patrocinio, challenge;
> create table challenge(
>   id smallint,plr_id smallint,acc_type_id smallint,
>   season char(4), year year, char_id smallint );
> insert into challenge values
> (  1 ,   1 ,  1 , 'Fall'  , 2015 ,
> 1175),
> (  2 ,   1 ,  4 , 'Fall'  , 2015 ,
> 1175),
> (  3 ,   1 ,  3 , 'Fall'  , 2015 ,
> 1175),
> (  4 ,   1 , 10 , 'Fall'  , 2015 ,
> 1175 ),
> (  5 ,   1 , 13 , 'Fall'  , 2015 ,
> 1175 ),
> (  6 ,   1 ,  2 , 'Fall'  , 2015 ,
> 1175);
> create table patrocinio(
>   id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) );
> insert into patrocinio values
> (  1 , 1 , 1 ,   1.00  ),
> (  2 , 4 , 3 ,   2.00  ),
> (  3 , 3 , 6 ,   1.00  );
>
> select c.id , p.chlng_id
> from challenge c
> left join patrocinio p on c.id=p.chlng_id ;
> +--+--+
> | id   | chlng_id |
> +--+--+
> |1 |1 |
> |3 |3 |
> |6 |6 |
> |2 | NULL |
> |4 | NULL |
> |5 | NULL |
> +--+--+
>
> select c.id , p.chlng_id
> from challenge c
> left join patrocinio p on c.id=p.chlng_id
> where p.chlng_id is null;
> +--+--+
> | id   | chlng_id |
> +--+--+
> |2 | NULL |
> |4 | NULL |
> |5 | NULL |
> +--+--+
>
> PB
>
>
>
>
>
>

ENUM() vs TINYINT

[Richard Reina]

I have a column name quarter which I need to have 5 possible inputs; 1, 2,
3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
Hence, I am also thus considering ENUM('first', 'second', 'third',
'fourth', 'overtime') as the input will primarily be used in written
descriptions. Is this a wise or unwise way to design a table column?

Thanks

Trouble with LEFT JOIN

[Richard Reina]

I have the following two tables;

mysql> select * from challenge;
+++-++--+-+--+
| ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
+++-++--+-+--+
|  1 |   1 |  1 | Fall  | 2015 |   1175
|
|  2 |   1 |  4 | Fall  | 2015 |   1175
|
|  3 |   1 |  3 | Fall  | 2015 |   1175
|
|  4 |   1 |10 | Fall  | 2015 |   1175 |
|  5 |   1 |13 | Fall  | 2015 |   1175 |
|  6 |   1 |  2 | Fall  | 2015 |   1175
|
++--+--++---+-+
6 rows in set (0.00 sec)

mysql> select * from patrocinio;
+++---+-+
| ID | PTRN_ID | CHLNG_ID | AMOUNT |
+++---+-+
|  1 | 1 | 1 |   1.00  |
|  2 | 4 | 3 |   2.00  |
|  3 | 3 | 6 |   1.00  |
++---+-++
I would like to select all rows from challenges which are NOT linked to a
patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
challenges.

I am trying to go about this with a LEFT JOIN query but it does not seem to
be working for me.

mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;
Empty set (0.00 sec)

Instead of getting rows 2 through 6 of challenges I get no rows.

Any help on how to correctly do this query would be greatly appreciated.

Thanks

Re: Trouble with LEFT JOIN

[Richard Reina]

2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:

> On 2015-09-04 9:40 AM, Richard Reina wrote:
>
>> I have the following two tables;
>>
>> mysql> select * from challenge;
>> +++-++--+-+--+
>> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID |
>> +++-++--+-+--+
>> |  1 |   1 |  1 | Fall  | 2015 |
>>  1175
>> |
>> |  2 |   1 |  4 | Fall  | 2015 |
>>  1175
>> |
>> |  3 |   1 |  3 | Fall  | 2015 |
>>  1175
>> |
>> |  4 |   1 |10 | Fall  | 2015 |
>>  1175 |
>> |  5 |   1 |13 | Fall  | 2015 |
>>  1175 |
>> |  6 |   1 |  2 | Fall  | 2015 |
>>  1175
>> |
>> ++--+--++---+-+
>> 6 rows in set (0.00 sec)
>>
>> mysql> select * from patrocinio;
>> +++---+-+
>> | ID | PTRN_ID | CHLNG_ID | AMOUNT |
>> +++---+-+
>> |  1 | 1 | 1 |   1.00  |
>> |  2 | 4 | 3 |   2.00  |
>> |  3 | 3 | 6 |   1.00  |
>> ++---+-++
>> I would like to select all rows from challenges which are NOT linked to a
>> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of
>> challenges.
>>
>> I am trying to go about this with a LEFT JOIN query but it does not seem
>> to
>> be working for me.
>>
>> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON
>> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1;
>>
>
> ... where p.chlng_id IS NULL;
>
> (Look up exclusion joins)
>
> PB
>
> -
>
>
> Empty set (0.00 sec)
>>
>> Instead of getting rows 2 through 6 of challenges I get no rows.
>>
>> Any help on how to correctly do this query would be greatly appreciated.
>>
>> Thanks
>>
>>
> Got it to work this way:

SELECT c.ID AS ID
FROM challenge c
WHERE c.ID NOT IN ( SELECT ID from patrocinio p WHERE p.PTRN_ID=1 );

When to create a new user?

[Richard Reina]

I am writing a web application in perl that will create, edit, update and
delete data from a MySQL database. I have written a perl module that will
manage the connections (issue database handles ). As new users sign up for
the application should each get their own MySQL username and password or is
okay to execute their queries with the same (one generic) MySQL username
and password?

Thanks

table desin question

[Richard Reina]

I am ceating a database application with two different types of users,
clients and technicians.  Both types of users have to create and account in
which they become users. From there they can become clients or
technicians or perhaps even both.  Since each type describe different
attributes -- user describes users basic information and login credentials,
client describes client information such as billing info and payment method
and technician describes technican information such as areas of expertese,
experience and qualifications -- would it be best to create three tables
and use user ID as the primary key for all?

For example:

TABLE: user
| ID | first_name| last_name| email |phone| password |

TABLE client
|ID  | billing_add | b_city | b_st | b_zip | pmnt_mthd | cc_no|

TABLE Techician
|ID  | type  | years_of_exp | current | zone |

Would this be the best way to design the schema and would it be best to
make the client ID and technician ID the same as the user ID as they relate
to the same person?

Re: table design question

[Richard Reina]

Hi Peter,

Thanks for the reply. So are you saying like this?

Repair
RID INT, Date DATE, Tech_ID INT, R_TYPE_ID INT
1 2015-07-28  3243  3
2 2015-06-15  1253  1


Repair_details
ID, APL_TYPE VARCHAR(35), REPAIR_CODE CHAR(4), DESC
1   Refridgerator
C compressor
2   Wash Mach
MC Motor Coupler
3   Dish Washer
SA  Spray Arm
4   Refridgerator
DP  Drain Pan

Not sure what you mean by repeating details.What would the look up table
look like?

Thanks

2015-07-29 9:38 GMT-05:00 peter.braw...@earthlink.net:

  one table with a long ENUM column that contains repairs that
  could be attributed to any appliance or different repair tables
  for each appliance.

 The first would stick you with extending the enum column forever,  the
 second would stick you with unmanageable table glut, so you need the
 traditional relational solution---a parent repairs table to track common
 repair attributes, a child table to track the details, and lookup tables to
 track repeating details.

 PB

  Original Message 
 From: Richard Reina gatorre...@gmail.com
 Reply-To: Richard Reina gatorre...@gmail.com
 Date: 07/29/15 10:19 AM
 To: mysql@lists.mysql.com mysql@lists.mysql.com
 Cc:
 Sub: table design question
 If I were to create a database table(s) to tract most common repairs to
 different appliances I can't decide if it would be better to create one
 table with a long ENUM column that contains repairs that could be
 attributed to any appliance or different repair tables for each appliance.
 All the tables would describe the same thing -- a repair -- however the the
 things being repaired are different in nature which means a great deal of
 types of repairs that do not relate. Here is an example.

 repair_wash_mach
 ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'leak', 'motor_coupler',
 'pump', 'controls', 'agitator')

 repair_dish_washer
 ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'drain_arm', 'drive_belt',
 'door_latch', 'spray_arm', 'drain_valve')

 repair_refridgerator
 ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'circ_fan', 'compressor',
 'disps_line', 'drain_pan', 'feeler_arm')

 Or since they are all repairs should they be in one table with a REALLY
 long ENUM table -- that will need to me altered as the number of appliances
 will most likely increase?

 ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT,  Type ENUM( 'leak',
 'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt',
 'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor',
 'disps_line', 'drain_pan', 'feeler_arm')

  End Original Message 

table design question

[Richard Reina]

If I were to create a database table(s) to tract most common repairs to
different appliances I can't decide if it would be better to create one
table with a long ENUM column that contains repairs that could be
attributed to any appliance or different repair tables for each appliance.
All the tables would describe the same thing -- a repair -- however the the
things being repaired are different in nature which means a great deal of
types of repairs that do not relate. Here is an example.

repair_wash_mach
ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'leak', 'motor_coupler',
'pump', 'controls', 'agitator')

repair_dish_washer
ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'drain_arm', 'drive_belt',
'door_latch', 'spray_arm', 'drain_valve')

repair_refridgerator
ID INT, Date DATE, Tech_ID INT,  Type ENUM( 'circ_fan', 'compressor',
'disps_line', 'drain_pan', 'feeler_arm')

Or since they are all repairs should they be in one table with a REALLY
long ENUM table -- that will need to me altered as the number of appliances
will most likely increase?

ID INT, APPLIANCE VARCHAR(35), Date DATE, Tech_ID INT,  Type ENUM( 'leak',
'motor_coupler', 'pump', 'controls', 'agitator', 'drain_arm', 'drive_belt',
'door_latch', 'spray_arm', 'drain_valve', 'circ_fan', 'compressor',
'disps_line', 'drain_pan', 'feeler_arm')

Specking a small MySQL server

[Richard Reina]

I have used MySQL for about twelve years as a database on our private LAN
that has only a handful of users at a time that query about a dozen
databases. The current server is an old rack-mounted machine that is
somewhat of an energy hog and is due to be replaced. I was considering
replacing it with a lap-top so as to conserve energy and because a laptop
has a built in battery backup. Currently I have a couple of laptops running
as slaves. Can anyone advise as to a good reliable brand of laptop to run
Linux and MySQL for this purpose. As this will by my main server I was
looking for something reliable.

Thanks for any ideas or insights.

Richard

Re: replication question replacing the master

[Richard Reina]

Manuel,

Thank you very much for this information. This sounds like a very good 
strategy. I think I will try switching some slaves from one relay to another to 
familiarize myself and get practice and them do it to deploy a new master.

Again, thank you very much.

Richard 



 El Jan 18, 2014, a las 2:00 AM, Manuel Arostegui man...@tuenti.com escribió:
 
 
 
 
 2014/1/17 Richard Reina gatorre...@gmail.com
 I have 3 relay MySQL database servers on my small office LAN backing up a
 master and 3 more machines backing up each relay (1 each). They are all
 replicating all databases and all tables. The master although running fine
 is almost eight years old. I'm thinking it's probably time to make one of
 the relays the master, but I've never done this before.
 
 I want the new master to have the IP address of the old master 192.168.0.1
 . To make the change I was going to take the master off the LAN and
 shutdown mysql on all the machines, change the IP address on the chosen
 relay to that of the master 192.168.0.1, then restart mysql on all the
 machines. I always refer to the machines by their IP addresses and never by
 their hostnames. Once I successfully make the change I was planning on
 making the old master a relay since it is still working fine.
 
 Will this plan work ok? Is there a better or easier way?
 
 If the three machines are sync'ed and have consistent data I don't see the 
 need of stopping MySQL:
 
 - Stop whatever writes to your current master
 - Once you are completely sure there are no writes in your current master, 
 set it to read_only = ON
 - In the slave which will become the master, get the logfile and current 
 position with: show master status;
  - Set the new IP in the new master
 
 
 Using the position taken in the new master go to the slaves machines and:
 
 stop slave; change master to master_host='IP', 
 master_log_file='whatever_file_name_you_got', 
 master_log_pos=whatever_number_you_got, 
 master_user='replication_or_whatever_you_have', 
 master_password='replication_or_whatever_you_have'; start slave;
 
 - Set read_only = OFF in your new master
 - Start your application so you can start getting writes again.
 
 As soon as you get writes if you do a show master status; in the new master 
 you should see the position going forward.
 
 I see that faster than any other thing.
  
 Hope this helps
 Manuel.
 

replication question replacing the master

[Richard Reina]

I have 3 relay MySQL database servers on my small office LAN backing up a
master and 3 more machines backing up each relay (1 each). They are all
replicating all databases and all tables. The master although running fine
is almost eight years old. I'm thinking it's probably time to make one of
the relays the master, but I've never done this before.

I want the new master to have the IP address of the old master 192.168.0.1
. To make the change I was going to take the master off the LAN and
shutdown mysql on all the machines, change the IP address on the chosen
relay to that of the master 192.168.0.1, then restart mysql on all the
machines. I always refer to the machines by their IP addresses and never by
their hostnames. Once I successfully make the change I was planning on
making the old master a relay since it is still working fine.

Will this plan work ok? Is there a better or easier way?

Thanks for you attention.

Richard

Re: Chain Replication QUestion

[Richard Reina]

To activate log-slave-updates do I just add log-slave-updates to the
my.cnf file?



2013/4/30, Manuel Arostegui man...@tuenti.com:
 2013/4/30 Richard Reina gatorre...@gmail.com

 I have a few slaves set up on my local network that get updates from
 my main mysql database master. I was hoping to turn one into a master
 while keeping it a slave so that I can set up a chain.  Does anyone
 know where I can find a how to or other documentation for this
 specific task?


 It is quite easy:

 Enable log-slave-updates in the slave you want to be a master.
 Do a mysqldump -e --master-data=2 and put that mysqldump in the future
 slaves. Take a look at the first lines of the mysqldump where you'll find
 the position and logfile those slaves need to start the replication from.
 You can also use xtrabackup if you like.

 Manuel.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Chain Replication QUestion

[Richard Reina]

Hello Manuel,

Thank you for your reply. Could I do the following?:

1) Enable log-bin on master2 (slave that will be converted to a master)
2) Enable log-slave-updates on master2
3) Execute CHANGE MASTER to on another existing slave so that it gets
it's updates from master2 instead of master1.

Thanks for the help thus far.


2013/4/30, Manuel Arostegui man...@tuenti.com:
 2013/4/30 Richard Reina gatorre...@gmail.com

 I have a few slaves set up on my local network that get updates from
 my main mysql database master. I was hoping to turn one into a master
 while keeping it a slave so that I can set up a chain.  Does anyone
 know where I can find a how to or other documentation for this
 specific task?


 It is quite easy:

 Enable log-slave-updates in the slave you want to be a master.
 Do a mysqldump -e --master-data=2 and put that mysqldump in the future
 slaves. Take a look at the first lines of the mysqldump where you'll find
 the position and logfile those slaves need to start the replication from.
 You can also use xtrabackup if you like.

 Manuel.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query Help

[Richard Reina]

Perfect! Thank you Larry et all.

Have a great weekend.


2013/4/19 Larry Martell larry.mart...@gmail.com

 On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina gatorre...@gmail.com
 wrote:
  Hello All,
 
  Happy Friday! I know how to do the following query:
 
 select count(*) from sales where WEEK(sale_date)=15 AND
  YEAR(sale_date)=2013;
 
  But can someone tell me I can do a query that will give me:
 
  the count(*) for each week of 2013 so that I end up with:
 
  WEEK | COUNT
  1   | 22
  2   | 31
  3   | 29
  etc...

 select WEEK(sale_date), count(*) from sales where YEAR(sale_date)=2013
 group by WEEK(sale_date);

Query Help

[Richard Reina]

Hello All,

Happy Friday! I know how to do the following query:

select count(*) from sales where WEEK(sale_date)=15 AND
YEAR(sale_date)=2013;

But can someone tell me I can do a query that will give me:

the count(*) for each week of 2013 so that I end up with:

WEEK | COUNT
1   | 22
2   | 31
3   | 29
etc...

Thanks,

Richard

Determing number of queries

[Richard Reina]

I am looking to spec out hardware for a new database server. I figured
a good starting point would be to find out how much usage my current
server is getting. It just a local machine that runs mysql and is
queried by a few users here in the office. Is there a way that mysql
can tell me info about it's workload?

Thanks

Richard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Please Help. selectcol_arrayref problem

[Richard Reina]

use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);

#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);

my $dbs = $dbh-selectcol_arrayref('show databases');

print @$dbs\n;

When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.

Any ideas?

Thanks

Re: Please Help. selectcol_arrayref problem

[Richard Reina]

I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED
BY 'psswd';

on the master. Doesn't *.* mean everything? Why would it just show me to
databases?






2013/4/2 Larry Martell larry.mart...@gmail.com

 On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
 wrote:
  use DBI;
  my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw,
 {
  RaiseError = 3 } );
  my $dbs = $dbh-selectcol_arrayref(show databases);
 
  #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
  #my $dbh = DBI-connect($dsn, $usrr, $passw);
 
  my $dbs = $dbh-selectcol_arrayref('show databases');
 
  print @$dbs\n;
 
  When I query the server for a list of databases with the code above it
  returns the name of just two and there are over 10.
 
  Any ideas?

 Permissions - the user you're logging in as probably only has
 permission to see the 2 that are being returned.

query help

[Richard Reina]

I have a table like this:

|ORDERS|
|ID| DATE | QNT | LEAD |
|342  | 8-12-12 | 32   | F|
|345  | 8-15-12 | 12   | S|
|349  | 8-16-12 | 9 | R|

I am looking for a way to query it with counts by the LEAD column in
order to tell what the number of each type lead is, so that I get
something like this:

F_LEADS  S_LEADS  R_LEADS
 44 122 32

Is this possible?  If so can anyone help with syntax?

Thanks,

Richard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Replication question: How to make a slave a master?

[Richard Reina]

I have a couple of mysql database slaves and would like to make one of them
be a master as well so that I can set another machine to replicate from it.
Can anyone tell me how I should go about it or know of any howtos for this
specific task?

Thanks,

Richard

Strange Replication Behavior

[Richard Reina]

I am trying to setup a new slave server and when I go to the master to
copy over master master.info and relay-log.info they seem to be
missing? Not in /var/lib/mysql  and could not find it with #: find /
-name master.info
The master server has been setup for years and already has three
slaves replicating from it so I cannot understand how this is
possible.  When I do the query  show master status

I get mysql_master_log.000123 | 755522343

Anyone have any idea as to what could be wrong?

Thanks,

Richard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

query problem with null

[Richard Reina]

 When I do the following query:

SELECT * FROM geo_trivia WHERE city IS NULL;

certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected when I run the above
query.  Can anyone help?

Re: query problem with null

[Richard Reina]

Ahhh... Thank you, that was exactly what the problem was. I will fix the
code that is setting the value of these new records to 'NULL'.

Thank you.

2012/3/9 David Lerer dle...@us.univision.com

 Have you tried to set city = null   (i.e. without the quotes)? David.



 -Original Message-
 From: Richard Reina [mailto:gatorre...@gmail.com]
 Sent: Friday, March 09, 2012 4:24 PM
 To: mysql@lists.mysql.com
 Subject: query problem with null

  When I do the following query:

 SELECT * FROM geo_trivia WHERE city IS NULL;

 certain columns that DO have 'NULL' value for city and not a '' (blank)
 value do not show up.
 I have even gone to the extent of reseting these records value as ='NULL'
 with UPDATE and they are still are not selected when I run the above
 query.  Can anyone help?

 The information contained in this e-mail and any attached
 documents may be privileged, confidential and protected from
 disclosure.  If you are not the intended recipient you may not
 read, copy, distribute or use this information.  If you have
 received this communication in error, please notify the sender
 immediately by replying to this message and then delete it
 from your system.

query question

[Richard Reina]

Hello All,

Hope everyone is having a wonderful holiday.  I have a table like:

|ID  |city|ST  |memo|

I would like to write a query that somewhat randomly grabs a record for a
for a given city and state. I say randomly because what I'm specifically
after is that if city IS NOT NULL than I want it to grab a record for that
city count(*)/10 of the times.  So that if there are four records for
Carmel, IN then a record that has city Carmel and ST IN will get
picked 40% of the time and record with IN and city=NULL will get picked
60% of the time.  Is this possible in a query or do I need to write code
that will select a query?

Thanks you,

Richard Reina

table design question

[Richard Reina]

I want to create a US geography database. So far I have categories such as
state nick names (some states have more than one), state mottos (text 25 to
150 characters), state name origins (100-300 characters), state trivial
facts, entry into union.  My question is; would it be better to keep at
least some of this information in separate tables like:

state_basic
ID | name | Incorporation | Entry in Union| Name_origin | Motto

state_nicknames
ID | name | nick_name|

state_trivia
ID | name | fact

or would it be batter for queries to try to put all this information in one
table?

Thanks,

Richard

Re: table design question

[Richard Reina]

Thank you very much for all the insightful advice. I will keep the
separated.

2011/9/19 Jerry Schwartz je...@gii.co.jp

 -Original Message-
 From: Richard Reina [mailto:gatorre...@gmail.com]
 Sent: Monday, September 19, 2011 9:55 AM
 To: mysql@lists.mysql.com
 Subject: table design question
 
 I want to create a US geography database. So far I have categories such as
 state nick names (some states have more than one), state mottos (text 25
 to
 150 characters), state name origins (100-300 characters), state trivial
 facts, entry into union.  My question is; would it be better to keep at
 least some of this information in separate tables like:
 
 state_basic
 ID | name | Incorporation | Entry in Union| Name_origin | Motto
 
 state_nicknames
 ID | name | nick_name|
 
 state_trivia
 ID | name | fact
 
 or would it be batter for queries to try to put all this information in
 one
 table?
 
 [JS] Use separate tables. Unless you have a //very// good reason, you
 should
 always try to normalize your data.

 In other words, use separate tables unless you are positive that you will
 //always// have 1:1 relationships between the various fields. For example,
 even such a simple thing as the data of incorporation might have more than
 one
 value in the case of the original colonies, the independent republics
 (Texas,
 California), and (I'm not sure about these) the Dakotas and West Virginia.

 Did you know that Maine was once part of Massachusetts? You could put that
 kind of thing into a trivia record, but that might make it harder to use in
 the future. My personal philosophy is that it is easier to scramble an egg
 than to unscramble it. You might someday need to keep track of which states
 were originally part of other states.

 And remember, those things that will never happen will happen the day
 before
 your vacation. The last thing you want to hear is Richard, before you
 leave I
 need you to... (I have 45 years of experience with that.)


 Regards,

 Jerry Schwartz
 Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341
 E-mail: je...@gii.co.jp
 Web site: www.giiresearch.com

Re: Using @ variables with LIKE,CONCAT

[Richard Bensley]

Hi, I just tried this on a schema I had laying about and it worked fine:

mysql SET @dude='pilgrim';
Query OK, 0 rows affected (0.00 sec)

mysql SELECT namefield FROM mytable WHERE namefield LIKE
CONCAT('%',@dude,'%');
+---+
| name  |
+---+
| Blood Elf Pilgrim |
| Blood Elf Pilgrim |
| Draenei Pilgrim   |
| High Elf Pilgrim  |
| Pilgrim Gal'ressa |
| Recovering Pilgrim|
| Wounded Blood Elf Pilgrim |
| Young Pilgrim |
+---+

I am running 5.5.6 x64 on Mac OS X.

Rich


On 11 May 2011 20:03, Hank hes...@gmail.com wrote:

 This used to work fine in Mysql 4.3, but no longer works in 5.5.8:

 set @txt='needle';
 select * from table where field  like CONCAT('%',@txt,'%');

 --returns the null set.  If I substitute like this:

 select * from table where field  like '%needle%';

 it works perfectly (and as it did in 4.x).

 How can I get this to work in 5.5.x?

 Thanks,

 -Hank




-- 
*Richard Bensley*
*Database Administrator*
*
*
richard.bens...@photobox.com
skype: richardbensley
Mobile: 07540878285

Re: function to limit value of integer

[Richard Reina]

Hi Travis,

This is very helpful thank you.  However, is there a way to make it not be
less than a 1.  As it's written below someone with one job gets a zero and
someone with no jobs gets a NULL.  It would be great if someone with  1 job
got a 1 and someone with zero jobs got a 0.

Thanks again,

Richard

2011/2/10 Travis Ard travis_...@hotmail.com

 Maybe some sort of logarithmic expression?

 select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight
 from data;

 Of course, you'd have to tweak your coefficients to match the weighting
 system you want to use.

 -Travis

 -Original Message-
 From: Richard Reina [mailto:gatorre...@gmail.com]
 Sent: Thursday, February 10, 2011 3:07 PM
 To: mysql@lists.mysql.com
 Subject: function to limit value of integer

 Is there a function that can limit the value of an integer in a MySQL
 query?   I am trying to write a query that scores someones experience.
 However, number of jobs can become overweighted in the the query below. If
 someone has done 10 jobs vs. 1 that's a big difference in experience. But
 someone who's done 100 vs. someone who's done 50 the difference in
 experience is not so great as they are both near the top of the learning
 curve.  In essence number of jobs becomes less and less of a contributor as
 it increases. Is there a way to limit it's value as it increases?

 SELECT years_srvd + no_of_jobs AS EXPERIENCE

 Thanks,

 Richard

function to limit value of integer

[Richard Reina]

Is there a function that can limit the value of an integer in a MySQL
query?   I am trying to write a query that scores someones experience.
However, number of jobs can become overweighted in the the query below. If
someone has done 10 jobs vs. 1 that's a big difference in experience. But
someone who's done 100 vs. someone who's done 50 the difference in
experience is not so great as they are both near the top of the learning
curve.  In essence number of jobs becomes less and less of a contributor as
it increases. Is there a way to limit it's value as it increases?

SELECT years_srvd + no_of_jobs AS EXPERIENCE

Thanks,

Richard

Help with ORDER BY

[Richard Reina]

I currently have a query that organizes search results for volunteers that
should be called for projects based on how close they live to a project the
and there past attendance.

Currently doing

SELECT name, city, state, phone, prods_done, cancels, miles
FROM volunteer_search
WHERE  project_id = 5653
ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC;

The results look something like this:

Jim Barnes Chicago, IL 773-555-   2  1  11.5
Kelley Smith Cicero, IL 708-444-2121   3  0  21.6
Kim Ayers Plainfield, IL 630-888-9898 22 1  25.1

I am trying to find a way to give more weight to reliability (projects done
and cancels).

For example the fact that Kim Ayers has done 22 projects makes her more
desirable than either Jim Barnes and Kelley Smith even though she has 1
cancel is farther from the project than both the other candidates.  The
desired order would be:

Kim Ayers Plainfield, IL 630-888-9898 22 1  25.1
Kelley Smith Cicero, IL 708-444-2121   3  0  21.6
Jim Barnes Chicago, IL 773-555-   2  1  11.5

Can the query could be modified to take into account such considerations?  I
realize that I have not really quantified how much reliability and the
number of projects done trumps distance from the project, but in the
beginning I am willing to set that somewhat arbitrary and adjust it later.
I realize that this may be beyond the scope of a MySQL query.  If so I am
grateful for any ideas on where to go for info/help.

Thanks,

Richard

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[Richard Quadling]

On 20 January 2011 19:20, Dotan Cohen dotanco...@gmail.com wrote:
 On Thu, Jan 20, 2011 at 19:21, Richard Quadling rquadl...@gmail.com wrote:
 That is terrific, at least the first half. The second half, with the
 Venn diagrams, is awkward!

 When you get heavily nested data, the adjacent list model (where you
 have a parentid for every uniqueid), you very quickly get into
 complicated logic trying to traverse n-levels. The nested set model is
 specifically built to handle this issue. I'd recommend getting to
 grips with it. It will make finding items belonging to a group (or a
 super group) a LOT easier.

 Especially if you have multiple tag hierarchies.


 Is that strategy widely deployed, then? It seems so unruly having to
 change on average half the database records for every new leaf.


 --
 Dotan Cohen

 http://gibberish.co.il
 http://what-is-what.com


Changing data in a database is the role of the database engine. It is
much more efficient to have the cost on the insert than it is on the
select.

The adjacent list model is very expensive at n-levels for the select,
but trivial cost for the insert. If you are inserting millions of rows
but only occasionally looking at the data, then stick with the
adjacent list model. But if tags and n-levels are regularly accessed
and form a main part to the functionality of the app, then you may
want to reconsider.

Sure, the insert for the nested set model is more expensive in terms
of the number of rows to amend, but indexing will certainly should
certainly help. If you have tools to help optimize the tag table and
the queries you use, then I'd follow the recommendations (I use MS
SQL, so my Query Optimization tools help me here). The nested set
model is extremely efficient on the select.

It is a trade off that you have to decide upon, based upon your data
and needs. If, as I suspect, you are going to be doing a LOT of
selects on the tags and (in the future) to multiple levels, then this
aspect needs to be very efficient.

For me it is well worth the effort of moving from the adjacent list
model to the nested set model.

Both mechanisms work. In my opinion, the adjacent list model is for
truly simply lookups, not for complicated n-levels.

One of the changes I made to the nested set model was for a Bill Of
Materials module. The client made complex machinery (industrial
lathes). The sum quantity for all the parts were in the 20,000 region.
Each nut, bolt, screw, etc. Many sub-assemblies. The parent/child node
logic was massive in dealing with retrieving questions like How many
machines can we build?, What stock do we need to buy/make to
complete an order of 20 lathes?. Lot's of recursion into each level
to build the list. Getting the results would take 3 or 4 minutes (this
is in a non SQL environment using a peer-to-peer modified D-ISAM
database - it was already slow because of all that). When I moved to
the nested set model, no recursion and 1 query (more or less) and I
have all the results I needed. It was seconds in comparison.

Regards,

Richard.
-- 
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[Richard Quadling]

On 20 January 2011 14:32, Dotan Cohen dotanco...@gmail.com wrote:
 I am designing an application that make heavy usage of one-to-many
 tags for items. That is, each item can have multiple tags, and there
 are tens of tags (likely to grow to hundreds). Most operation on the
 database are expected to be searches for the items that have a
 particular tag. That is, users will search per tags, not per items.

 These are the ways that I've thought about storing the tags, some bad
 and some worse. If there is a better way I'd love to know.

 1) Each item will get a row in a tags table, with a column for each tag.
 mysql CREATE TABLE tags (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item VARCHAR(100),
    tag1 bool,
    tag2 bool,
    
    tagN bool
 );

 With this approach I would be adding a new column every time a new
 category is added. This looks to me a good way given that users will
 be searching per tag and a simple SELECT item FROM tags WHERE
 tag1=true; is an easy, inexpensive query. This table will get very
 large, there will likely be literally thousands of items (there will
 exist more items than tags).



 2) Store the applicable tags one per line in a text field in the items table.
 mysql CREATE TABLE items (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item VARCHAR(100),
    tags text,
 );

 This looks like a bad idea, searching by tag will be a mess.



 3) Store the tags in a table and add items to a text field. For instance:
 mysql CREATE TABLE tags (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tagName VARCHAR(100),
    items text,
 );

 This looks to be the best way from a MySQL data retrieval perspective,
 but I do not know how expensive it will be to then split the items in
 PHP. Furthermore, adding items to tags could get real expensive.



 Caveat: at some point in the future there may be added the ability to
 have a tag hierarchy. For instance, there could exist a tag
 restaurant that will get the subtags italian and french. I could
 fake this with any approach by having a table of existing tags with a
 parentTag field, so if I plan on having this table anyway would
 method 3 above be preferable?

 Note: this message is cross-posted to the MySQL and the PHP lists as I
 am really not sure where is the best place to do the logic. My
 apologies to those who receive the message twice.

 Thanks!

 --
 Dotan Cohen

 http://gibberish.co.il
 http://what-is-what.com

 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



I'd have my items table, my tags table and a join table for the two.
My join table is really simple. UniqueID, ItemID, TagID.

I'd recommend using a nested set approach for the tags
(http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
gives a good explanation on the issues and methodology of nested
sets).



-- 
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[Richard Quadling]

On 20 January 2011 16:20, Dotan Cohen dotanco...@gmail.com wrote:
 On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote:
 I'd have my items table, my tags table and a join table for the two.
 My join table is really simple. UniqueID, ItemID, TagID.


 Yes, that is the first approach that I mentioned. It looks to be a
 good compromise.


 I'd recommend using a nested set approach for the tags
 (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
 gives a good explanation on the issues and methodology of nested
 sets).


 That is terrific, at least the first half. The second half, with the
 Venn diagrams, is awkward!

 --
 Dotan Cohen

 http://gibberish.co.il
 http://what-is-what.com


When you get heavily nested data, the adjacent set model (where you
have a parentid for every uniqueid), you very quickly get into
complicated logic trying to traverse n-levels. The nested set model is
specifically built to handle this issue. I'd recommend getting to
grips with it. It will make finding items belonging to a group (or a
super group) a LOT easier.

Especially if you have multiple tag hierarchies.





-- 
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

replication question

[Richard Reina]

-- 
Richard Reina
Rush Logistics, Inc.
Watch our 3 minute movie: 
http://www.rushlogistics.com/movie


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Problems storing a blob using php.

[Richard Riley]

Debian squeeze, mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu
(i486) using readline 6.1, PHP 5.3.3-2 with Suhosin-Patch (cli) (built:
Oct 21 2010 18:58:27).

I'm not sure if this group or the php one is more appropriate but
thought I would try here first as it is perhaps a mysql setting I am
missing.

I have been unable to get prepared statements to store a blob.

using this test code:-

function saveImage($event)
{
$stmt = getDB()-prepare(INSERT INTO images (image) VALUES (?));
$null = NULL;
$stmt-bind_param(b, $null);
$fp = fopen(common-images/takeaway.png, r);
while (!feof($fp)) {
$stmt-send_long_data(0, fread($fp, 8192)); /* I also tried 
get_file_contents */
}
fclose($fp);
$stmt-execute();
logtext($stmt-error);
}


I get the following error in my home brew log file: Incorrect arguments to 
mysqld_stmt_execute

I have checked that fp is valid.

My table is defined as follows:-

,
| CREATE TABLE `images` (
|   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|   `fkey` int(10) unsigned DEFAULT NULL,
|   `image` blob,
|   `name` varchar(30) NOT NULL,
|   `type` varchar(30) NOT NULL,
|   PRIMARY KEY (`id`),
|   KEY `deleteimages` (`fkey`),
|   CONSTRAINT `deleteimages` FOREIGN KEY (`fkey`) REFERENCES `events` (`id`) 
ON DELETE CASCADE
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8
`

I'm sure it must be something glaringly obvious but being a bit of a
noob to mysql I'm not sure what to do to narrow it down. I can set the
blob field directly. Its the send_long_data that seems to cause the arg
error at the execute phase.

Any debugging hits/configure hints would be much appreciated.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: query help

[Richard Reina]

Thank you very much for all the insightful replies. I think I can get it to 
work with a join.  


 Joerg Bruehe joerg.bru...@sun.com wrote:

 Hi!
 
 
 Jay Blanchard wrote:
  [snip]
  I have a table similar to this:
  
   -
  |transactions |
  |ID   |DATE  |EMPLOYEE|
  |234  |2010-01-05| 345|
  |328  |2010-04-05| 344|
  |239  |2010-01-10| 344|
  
  Is there a way to query such a table to give the days of the year that
  employee 344 did not have a transaction?
  [/snip]
  
  SELECT DATE
  FROM transactions
  WHERE EMPLOYEE != '344'
  GROUP BY DATE;
 
 I strongly doubt this will work - what if several employees have
 transactions on the same day?
 
 No, what the poster effectively needs is a set difference:
 Take the set of all candidate dates, and subtract the set of days on
 which the employee in question did have a transaction.
 
 The first difficulty will be to construct the set of candidate dates, as
 this needs a decision what to do about non-working dates (weekends,
 public holidays, ...) and how to determine them - depending on the
 business logic, that set may be specific to the employee (personal
 vacation!).
 
 Only when this has been decided, there is the question how to implement
 the set difference:
 - SQL minus is a candidate, but MySQL doesn't support that AFAIK.
 - Outer Join is the other possibility, as proposed by Gavin.
 - Having all candidate dates in some temporary table and then deleting
   those with a transaction is another way, but probably very slow.
   (The advantage of this might be that it is the most flexible way.)
 
 
 Jörg
 
 -- 
 Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
 Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
 Geschaeftsfuehrer: Juergen Kunz
 Amtsgericht Muenchen: HRB161028
 
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

query help

[Richard Reina]

I have a table similar to this:

 -
|transactions |
|ID   |DATE  |EMPLOYEE|
|234  |2010-01-05| 345|
|328  |2010-04-05| 344|
|239  |2010-01-10| 344|

Is there a way to query such a table to give the days of the year that employee 
344 did not have a transaction?

Thanks for the help.

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

redirecting query output?

[Richard Reina]

I have a perl script that periodically reads and enters (via perl-DBI parsed 
system output (about received faxes) into a table.  Since the old output is 
repeated I have put, when creating the table, the UNIQUE key on the field of 
the faxname, which is always different (something like fax07879.tif) 

Every time the script is run many of the old faxes that have already been 
entered into the table are still on the server (as I would like them to be).  
As a result MySQL via execute() dutifully warns about all the duplicate errors. 
Is there anyway to redirect that output so the it does not appear as standard 
output? Or is it a bad idea to do so and should I being doing a different sort 
ow query altogether?

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

query help

[Richard Reina]

I am trying to write a query that merges 2 columns from different tables and 
show them as one column of data.  Something like the following.

payables
ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |

paychecks
ID  |check_no| amount
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

I am struggling to write a select query that gives me amounts and check numbers 
from both of the tables in the same column.  Like the following:

ID  |check_no| amount|
3   |3478| 67.00 |
4   |3489| 98.00 |
8   |3476| 56.00 |
23  |3469|498.00 |
34  |3502|767.00 |
36  |3504}754.00 |

Any help would be greatly appreciated.

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [PHP] Instll php on Window 2003 64Bit questions

[Richard Quadling]

2010/1/3 Ashley Sheridan a...@ashleysheridan.co.uk:
 On Sun, 2010-01-03 at 15:10 +0800, Edward S.P. Leong wrote:

 Dear All,

 If the OS is Windows 2003 64Bit (IIS)...
 So, which php package must download and how to config it for running
 with IIS ?
 Due to I don't quite the online manual:
 http://www.php.net/manual/en/install.windows.iis.php
 Which installation mode is suitable of it ?

 Thanks !

 Edward.



 Personally I'd go with a WAMP install instead. Apache is faster, less
 resource intensive, and more secure than IIS. You also have the benefit
 of all the Apache mods out there, like mod_rewrite, which I believe
 you'd have to pay for on an IIS server.

 Thanks,
 Ash
 http://www.ashleysheridan.co.uk




IIS7 has a URLRewrite module which is freely available via the Web
Platform Installer or via
http://learn.iis.net/page.aspx/460/using-url-rewrite-module

-- 
-
Richard Quadling
Standing on the shoulders of some very clever giants!
EE : http://www.experts-exchange.com/M_248814.html
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Query help

[Richard Reina]

I was wondering if someone could lend a hand with the following query.  I have 
table.

SEARCHES
|ID |trans_no|comp_id|result
13  | 455|  675| o
15  | 302|  675| o
16  | 455|  675| o
12  | 225|  629| y

SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY 
trans_no;

gives me a count of 3.

However, what I need is a count for how many different (unique) transactions 
company number 675 got a result 'o' which would be 2 (455  302).  I have tried 
different group by columns but to no avail. Can someone help?

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Cant get TRIM to work?

[Richard Reina]

Hello All,

I can't get trim to trim the blank space from a TEXT field in the query below 
and was wondering if someone could tell what I am doing wrong?

SELECT TRIM(notes) FROM work_notes;

Thanks for any help as I am at a complete loss.

Richard


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MySQL General Discussion question

[Richard Gagnon]

Sorting a varchar field alphabetically with correct numerical order help
needed

 

I have  a varchar 50 field that contains product names, which are typically
numerical, alphabetical and punctuation thrown in. I would like to have them
returned in some sort of order that is roughly alphabetical, but with the
numbers in numerical order. The basic Order By clause does not do it
correctly.

An example is:

 

SELECT setname  FROM sets ORDER BY setname

 

Sample values of setname are:
658
#1 JCAL
011
#2 NOV
#11 NOV
#12 NOV
985

ABC

#123 NOV

The results I get are:
#1 JCAL
#11 NOV
#12 NOV

#123 NOV
#2 NOVwrong
011
658
985

ABC

 

The results I want are:

#1 JCAL
#2 NOV  should be here
#11 NOV
#12 NOV

#123 NOV
011
658
985

ABC


In the above, the #2 JCAL should be second, otherwise, list is correct. I
could also live with the values beginning with # or any alpha character
coming after the numerical ones, but the 1, 11, 2, order is the issue.

 

Does anyone have any idea how to do this? I have been playing around with
various suggested ways, including casting and converting, but so far have
not been able to solve this. Any ideas would be greatly appreciated.

 

Thanks, Rich

 

column exists but unknown

[Richard Whitney]

Hello.

I am running MySQL 5.0.45
and have the following query and error:

SQL query:

SELECT SQL_CALC_FOUND_ROWS CONCAT( 'Edit Space' ) AS '', CONCAT( 'Edit
Status' ) AS '', space_id AS 'Space ID',
TYPE AS 'Ad Type', scheme AS 'Scheme', adman_ad_spaces.status_approved
AS 'Approval Status', adman_ad_spaces.status_paid AS 'Paid Status',
adman_ad_spaces.notes AS 'Notes', start_date AS 'Start Date', end_date
AS 'End Date*', adman_ad_spaces.inserted_on AS 'Created'
FROM adman_ad_spaces, adman_pricing_schemes
INNER JOIN adman_campaigns ON adman_campaigns.campaign_id =
adman_ad_spaces.campaign_id
INNER JOIN adman_users ON adman_users.user_id = adman_campaigns.user_id
WHERE adman_ad_spaces.status = 'Active'
AND adman_ad_spaces.scheme_id = adman_pricing_schemes.scheme_id
AND adman_ad_spaces.status_admin = 'Active'
AND adman_users.name LIKE '%dadasd%'
LIMIT 0 , 10

MySQL said:

#1054 - Unknown column 'adman_ad_spaces.campaign_id' in 'on clause'

part of the structure of adman_ad_spaces:


Field Type Collation Attributes Null Default Extra Action
space_id int(10)   UNSIGNED No  auto_increment
scheme_id int(10)  UNSIGNED No 0
campaign_id int(10)  UNSIGNED No 0
type enum('Text', 'Banner', 'Rich', 'Text in List') latin1_swedish_ci  No Text

as you can see campaign_id clearly exists in the table

Is my table corrupt?
Any ideas?

Thank you for your help.

Richard

--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559

You come up with the ideas, I come up with the solutions.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: column exists but unknown

[Richard Whitney]

Nevermind, the problem was not with the DB :\

On Tue, Mar 3, 2009 at 10:44 AM, Richard Whitney phpmy...@gmail.com wrote:
 Hello.

 I am running MySQL 5.0.45
 and have the following query and error:

 SQL query:

 SELECT SQL_CALC_FOUND_ROWS CONCAT( 'Edit Space' ) AS '', CONCAT( 'Edit
 Status' ) AS '', space_id AS 'Space ID',
 TYPE AS 'Ad Type', scheme AS 'Scheme', adman_ad_spaces.status_approved
 AS 'Approval Status', adman_ad_spaces.status_paid AS 'Paid Status',
 adman_ad_spaces.notes AS 'Notes', start_date AS 'Start Date', end_date
 AS 'End Date*', adman_ad_spaces.inserted_on AS 'Created'
 FROM adman_ad_spaces, adman_pricing_schemes
 INNER JOIN adman_campaigns ON adman_campaigns.campaign_id =
 adman_ad_spaces.campaign_id
 INNER JOIN adman_users ON adman_users.user_id = adman_campaigns.user_id
 WHERE adman_ad_spaces.status = 'Active'
 AND adman_ad_spaces.scheme_id = adman_pricing_schemes.scheme_id
 AND adman_ad_spaces.status_admin = 'Active'
 AND adman_users.name LIKE '%dadasd%'
 LIMIT 0 , 10

 MySQL said:

 #1054 - Unknown column 'adman_ad_spaces.campaign_id' in 'on clause'

 part of the structure of adman_ad_spaces:


 Field Type Collation Attributes Null Default Extra Action
 space_id int(10)   UNSIGNED No  auto_increment
 scheme_id int(10)  UNSIGNED No 0
 campaign_id int(10)  UNSIGNED No 0
 type enum('Text', 'Banner', 'Rich', 'Text in List') latin1_swedish_ci  No Text

 as you can see campaign_id clearly exists in the table

 Is my table corrupt?
 Any ideas?

 Thank you for your help.

 Richard

 --
 Richard Whitney
 phpmy...@gmail.com
 http://phpmydev.com
 Ofc. 602-288-5340
 Ofc. 877-624-6302
 Fax. 480-704-4559

 You come up with the ideas, I come up with the solutions.




-- 
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559

You come up with the ideas, I come up with the solutions.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot

[Richard Heyes]

So where's the advantage of VARCHAR ?

 Less space on disc = less data retrieved from disc = faster data
 retrieval - sometimes. If you have small columns, a small number of
 rows, or both, then char columns may be faster. If you have large
 columns of varying actual length, lots of rows, or both, then varchar
 columns may be faster.

I still think a CHAR field would be faster than a VARCHAR because of
the fixed row length (assuming every thing else is fixed). Perhaps
someone from the MySQL list could clarify...?

-- 
Richard Heyes

HTML5 Graphing for FF, Chrome, Opera and Safari:
http://www.rgraph.org (Updated January 4th)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Stopping DNS Lookups

[Richard S. Huntrods]

Yes, that's exactly what the link from Hassan said to do.

Interestingly, what is not stated in that link is that you must add 
entries in mysql tables 'db' and 'user' for '127.0.0.1' (a.k.a. 
'localhost') if you need to access the database from the local server. 
Remote access via static IP (assuming those entries were already in 'db' 
and 'user') work fine, but 'localhost' no longer works after the reboot.


Cheers,

-Richard

Moon's Father wrote:

Add skip-name-reslove in my.cnf and restart mysql immediately.

On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:


Awesome! Thanks very much - exactly what I was looking for. I'm in
the field and was under the gun, otherwise would have checked the
manuals first.

Again, thanks.

-Richard


Hassan Schroeder wrote:

On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:

 


Recently I had to start monitoring the firewall traffic on
this intranet,
and discovered the MySQL server is routinely sending
queries to the main DNS
server (outside the firewall). I suspect the server is
performing reverse
DNS lookups for some reason.

Is there a quick way of disabling these calls to the DNS
server?
   



See http://dev.mysql.com/doc/refman/5.0/en/dns.html

HTH,
 



-- 
MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql
To unsubscribe:  
 http://lists.mysql.com/[EMAIL PROTECTED]





--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stopping DNS Lookups

[Richard S. Huntrods]

Not on my testing system (Win-XP). I test on Win-XP but deploy on Unix. 
Not only that, but the production application servers are separate from 
the database server, so I never use localhost in production anyway.


But on the XP test server, I had to add the 127.0.0.1 entries to 'db' 
and 'user' - 'localhost' alone did not work. I did not test whether or 
not 'localhost' would work on a Unix server as this was not something I 
required. Would be a good test, though.


Cheers,

-Richard

Martin Gainty wrote:

Richard-

That should work..curious if localhost is mapped elsewhere?

Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official 
business of Sender. This transmission is of a confidential nature and 
Sender does not endorse distribution to any party other than intended 
recipient. Sender does not necessarily endorse content contained 
within this transmission.



 Date: Fri, 24 Oct 2008 07:51:44 -0700
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 CC: [EMAIL PROTECTED]; mysql@lists.mysql.com
 Subject: Re: Stopping DNS Lookups

 Yes, that's exactly what the link from Hassan said to do.

 Interestingly, what is not stated in that link is that you must add
 entries in mysql tables 'db' and 'user' for '127.0.0.1' (a.k.a.
 'localhost') if you need to access the database from the local server.
 Remote access via static IP (assuming those entries were already in 
'db'

 and 'user') work fine, but 'localhost' no longer works after the reboot.

 Cheers,

 -Richard

 Moon's Father wrote:
  Add skip-name-reslove in my.cnf and restart mysql immediately.
 
  On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods
  [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
 
  Awesome! Thanks very much - exactly what I was looking for. I'm in
  the field and was under the gun, otherwise would have checked the
  manuals first.
 
  Again, thanks.
 
  -Richard
 
 
  Hassan Schroeder wrote:
 
  On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods
  [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote:
 
 
 
  Recently I had to start monitoring the firewall traffic on
  this intranet,
  and discovered the MySQL server is routinely sending
  queries to the main DNS
  server (outside the firewall). I suspect the server is
  performing reverse
  DNS lookups for some reason.
 
  Is there a quick way of disabling these calls to the DNS
  server?
 
 
 
  See http://dev.mysql.com/doc/refman/5.0/en/dns.html
 
  HTH,
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
  --
  I'm a MySQL DBA in china.
  More about me just visit here:
  http://yueliangdao0608.cublog.cn

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]



You live life beyond your PC. So now Windows goes beyond your PC. See 
how http://clk.atdmt.com/MRT/go/115298556/direct/01/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Stopping DNS Lookups

[Richard S. Huntrods]

I have a server that runs only MySQL. It services several other servers 
inside a firewalled intranet. All connections to the MySQL database are 
done using static IP addresses.


Recently I had to start monitoring the firewall traffic on this 
intranet, and discovered the MySQL server is routinely sending queries 
to the main DNS server (outside the firewall). I suspect the server is 
performing reverse DNS lookups for some reason.


Is there a quick way of disabling these calls to the DNS server?

This is a pretty much stock installation of MySQL 5.0.51a on Solaris 10 
(AMD 64), 64 bit.


Thanks in advance,

-Richard


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stopping DNS Lookups

[Richard S. Huntrods]

[EMAIL PROTECTED] wrote:

Point to an internal dns server
  

We don't have one.

Is somebody with a weird hostname connecting to your server via ssh ?
  

Nope. This is occurring while the server's intranet is totally unmanned.

-Richard



--Original Message--
From: Richard S. Huntrods
To: mysql@lists.mysql.com
Sent: Oct 22, 2008 07:40
Subject: Stopping DNS Lookups

I have a server that runs only MySQL. It services several other servers 
inside a firewalled intranet. All connections to the MySQL database are 
done using static IP addresses.


Recently I had to start monitoring the firewall traffic on this 
intranet, and discovered the MySQL server is routinely sending queries 
to the main DNS server (outside the firewall). I suspect the server is 
performing reverse DNS lookups for some reason.


Is there a quick way of disabling these calls to the DNS server?

This is a pretty much stock installation of MySQL 5.0.51a on Solaris 10 
(AMD 64), 64 bit.


Thanks in advance,

-Richard


  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stopping DNS Lookups

[Richard S. Huntrods]

Awesome! Thanks very much - exactly what I was looking for. I'm in the 
field and was under the gun, otherwise would have checked the manuals first.


Again, thanks.

-Richard

Hassan Schroeder wrote:

On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods
[EMAIL PROTECTED] wrote:

  

Recently I had to start monitoring the firewall traffic on this intranet,
and discovered the MySQL server is routinely sending queries to the main DNS
server (outside the firewall). I suspect the server is performing reverse
DNS lookups for some reason.

Is there a quick way of disabling these calls to the DNS server?



See http://dev.mysql.com/doc/refman/5.0/en/dns.html

HTH,
  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Data files from 4.1.13 with 5.0.x

[Richard Heyes]

 Would data files from 4.1.13 work with 5.0.x or will I have to use an SQL 
 dump?

Well, not to worry, I managed to start 4.1.13 and got an SQL dump. Cheers.

-- 
Richard Heyes
http://www.phpguru.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Data files from 4.1.13 with 5.0.x

[Richard Heyes]

Hi,

Would data files from 4.1.13 work with 5.0.x or will I have to use an SQL dump?

Thanks.

-- 
Richard Heyes
http://www.phpguru.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Table aliasing

[Richard Heyes]

Hi,

Is there a way in MySQL to define an alias for a table, so in effect
it has two names? For migration purposes.

Thanks.

-- 
Richard Heyes
http://www.phpguru.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Table aliasing

[Richard Heyes]

No, the net effact would be that the table would have two names.

-- 
Richard Heyes
http://www.phpguru.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Table aliasing

[Richard Heyes]

 I think what you want is CREATE VIEW test.v AS SELECT * FROM t;

That will do the job, thank you.

-- 
Richard Heyes
http://www.phpguru.org

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select count(). Help-a-newb

[Richard]

Hi I think you would do this :

SELECT
sf_conferences.id,
sf_conferences.name,
count(*) AS `count`
FROM
((
LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk)
LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk)
LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk
GROUP BY
sf_conferences.id,
sf_conferences.name;

I hope this is what you want :)

contiw a écrit :

Is it possible to get the count() for forums, threads and messages
extrapolating from the following query? Thanx for helping a newb. 

select 
sf_conferences.id, 
sf_conferences.name 
from 
((#variables.tableprefix#conferences 
left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) 
left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) 
left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk 
GROUP BY 
sf_conferences.id, 
sf_conferences.name



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with ORDER BY using two colomns

[Richard]

Hello,
I've got a table which containes two date colomns.
The first one is called `date` and the second `update`
In the first one I put the ticket creation date, and on update I add or 
change the update value.
So the update colomn does not contain a value until the first update has 
been done.
I would like to order the tickets by their last update value. And if 
this value does not exist use the date value.


at the moment I use this :

ORDER BY `date` DESC
and I would like to replace it by something like this :

ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

I know this code is completly wrong, just to try and show you what I 
need ...


Here is an example of what I want to achieve

num |   date|   update
---
1   |   1   |   
2   |   10  |   60
3   |   20  |   
4   |   30  |   
5   |   40  |   90
6   |   50  |   

The required result would be :

num |   date|   update
---
5   |   40  |   90
2   |   10  |   60
6   |   50  |   
4   |   30  |   
3   |   20  |   
1   |   1   |   

Thanks in advance :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with ORDER BY using two colomns

[Richard]

Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` = '2' 
AND `update`  '.(time()-864000).') CASE WHEN `update` = '' THEN ORDER 
BY `date` DESC ELSE ORDER BY `update` DESC END CASE;


It does not work but, is it my code that is wrong or is it just that 
case does not work with mysql 4.1.11 ?


Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else created end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on update I add or
change the update value.
 So the update colomn does not contain a value until the first update has
been done.
 I would like to order the tickets by their last update value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks in advance :)

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with ORDER BY using two colomns

[Richard]

Thanks,

I think that your solution will be sufficient for my needs, however I 
would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days to the 
 update dates so they would place themselves in the correct position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to the 
update field and if the customer does not answer within 10 days, to re 
insert them into the list.


But as the update timestamp will be 10 days old, I would like to add 10 
days to the update while inserting them to the list (not changing the 
actual value inserted in the database just add 10 days during the 
reordering process.). I hope my explanation in understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it just that
case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else created
end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show
you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks in advance :)

 --
 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]




--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

[Richard]

Thanks, it works like a charm :)

Ben Clewett a écrit :

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, however I 
would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days to 
the  update dates so they would place themselves in the correct position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to the 
update field and if the customer does not answer within 10 days, to re 
insert them into the list.


But as the update timestamp will be 10 days old, I would like to add 
10 days to the update while inserting them to the list (not changing 
the actual value inserted in the database just add 10 days during the 
reordering process.). I hope my explanation in understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it just that
case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else created
end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show
you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks in advance :)

 --
 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]





--
.:: Rafael Barbolo Lopes ::.
http://barbolo.polinvencao.com/








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

[Richard]

Thanks,

This is for the unanswered list of questions, so the output list (not 
the list stored in the mysql database) should never go over 100.


by scalable, do you mean alot of ressources being used or a long wait 
for the answer? Because I belive I Could just use a simple limit if I 
needed to have a limited number of results on one page.


Every time a question is answered the update date will change, and the 
status could also change. So I don't see how to easily do this by 
creating another table.


Ben Clewett a écrit :

Richard,

No problem, glad it works.  But note: this is not scalable.  If you have 
more than a few hundred rows, you may want to think about a better 
solution, like storing the order field permanetly and giving it an index :)


Ben

Richard wrote:

Thanks, it works like a charm :)

Ben Clewett a écrit :

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, however 
I would still like to know for my personal knowledge how to manage 
correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days to 
the  update dates so they would place themselves in the correct 
position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to 
the update field and if the customer does not answer within 10 days, 
to re insert them into the list.


But as the update timestamp will be 10 days old, I would like to add 
10 days to the update while inserting them to the list (not changing 
the actual value inserted in the database just add 10 days during 
the reordering process.). I hope my explanation in understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR (`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` = ''
THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it just 
that

case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else 
created

end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER BY `date` DESC
 and I would like to replace it by something like this :

 ORDER (IF `update`!= '' BY UPDATE ELSE BY DATE)

 I know this code is completly wrong, just to try and show
you what I need
...

 Here is an example of what I want to achieve

 num |   date|   update
 ---
 1   |   1   |
 2   |   10  |   60
 3   |   20  |
 4   |   30  |
 5   |   40  |   90
 6   |   50  |

 The required result would be :

 num |   date|   update
 ---
 5   |   40  |   90
 2   |   10  |   60
 6   |   50  |
 4   |   30  |
 3   |   20  |
 1   |   1   |

 Thanks

Re: Help with ORDER BY using two colomns [ solved thankyou :) ]

[Richard]

Yes that would be easier, except that I would still have to create a 
tempory table to add 10 days onto the ones which have a status waiting 
for answer from customer and have not been answered for more than 10 days.


This system is for customers who do not have an account yet to contact 
me. And will only be used by me an my team. In normal usage I will not 
be expecting the table of unanswered messages to be any longer than 10 
or 20 lines,


So I will leave it be for the moment as it works exactly as I want it to 
and as it will be on a server with alot of free ressources.


Thanks for all your suggestions ! :)

Andy Wallace a écrit :

Not sure, but perhaps an even simpler method would be to consider the
initial insert an update as well... so the update column would always have
a value. Then the sort would (I believe) always be in the order you want,
and if you need to differentiate between rows that are new vs rows that
are updated, (date = update) = new. You can put an index on this field
and not have the performance issue to worry about.

Just a thought.

andy

Richard wrote:

Thanks,

This is for the unanswered list of questions, so the output list (not 
the list stored in the mysql database) should never go over 100.


by scalable, do you mean alot of ressources being used or a long wait 
for the answer? Because I belive I Could just use a simple limit if I 
needed to have a limited number of results on one page.


Every time a question is answered the update date will change, and the 
status could also change. So I don't see how to easily do this by 
creating another table.


Ben Clewett a écrit :

Richard,

No problem, glad it works.  But note: this is not scalable.  If you 
have more than a few hundred rows, you may want to think about a 
better solution, like storing the order field permanetly and giving 
it an index :)


Ben

Richard wrote:

Thanks, it works like a charm :)

Ben Clewett a écrit :

A modification to my last email, try:

SELECT
  *, IF(update != '', update + 10, date) AS o
FROM
  my_table
ORDER BY o DESC;

+-+--++--+
| num | date | update | o|
+-+--++--+
|   5 |   40 | 90 |  100 |
|   2 |   10 | 60 |   70 |
|   6 |   50 ||   50 |
|   4 |   30 ||   30 |
|   3 |   20 ||   20 |
|   1 |1 ||1 |
+-+--++--+


Richard wrote:

Thanks,

I think that your solution will be sufficient for my needs, 
however I would still like to know for my personal knowledge how 
to manage correctly this kind of need.


And to make it more complicated I've just rearlised that there is 
another element to take into account, I would need to add 10 days 
to the  update dates so they would place themselves in the correct 
position.


This is how I need the system to work :

Any new requests (without an update value) are ordered by date
I want to be able to answer these requests (adding a time stamp to 
the update field and if the customer does not answer within 10 
days, to re insert them into the list.


But as the update timestamp will be 10 days old, I would like to 
add 10 days to the update while inserting them to the list (not 
changing the actual value inserted in the database just add 10 
days during the reordering process.). I hope my explanation in 
understadable ...


:)

Rafael Barbolo Lopes a écrit :

Can't you do Something like:

ORDER BY (update,date)

The major column of ordering would be update and the second date.

I'm not sure about this solution

On Tue, Apr 8, 2008 at 8:54 AM, Richard [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED] wrote:


Hello I've tried the following with mysql 4.1.11

SELECT * FROM quick_contact WHERE (`status` = '0') OR 
(`status` =
'2' AND `update`  '.(time()-864000).') CASE WHEN `update` 
= ''

THEN ORDER BY `date` DESC ELSE ORDER BY `update` DESC END CASE;

It does not work but, is it my code that is wrong or is it 
just that

case does not work with mysql 4.1.11 ?

Thanks :)

Kristian Myllymäki a écrit :

mysql version?

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

order by case when updated is not null then updated else 
created

end desc;

/Kristian

On Tue, Apr 8, 2008 at 1:04 PM, Richard [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:

Hello,
 I've got a table which containes two date colomns.
 The first one is called `date` and the second `update`
 In the first one I put the ticket creation date, and on
update I add or
change the update value.
 So the update colomn does not contain a value until the
first update has
been done.
 I would like to order the tickets by their last update
value. And if this
value does not exist use the date value.

 at the moment I use this :

 ORDER

Re: Help with db design

[Richard Jones]

Baron Schwartz wrote:

Hi,



This is a fine place to ask such questions.

(In general you can just ask first, and people will tell you if you're
off-topic).



OK, thanks - I've posted the details to a new subject earlier today but 
it doesn't seem to have showed up yet.

--
Richard Jones


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with a complex data model

[Richard Jones]

I have a complex application under re-development, and am stuck on the 
data model which is almost certainly wrong. The application is designed 
to handle clinical requests coming into a hospital records system. A 
request concerns a single patient, and can come from a hospital or a GP 
(general practitioner). Details as follows:


1) The starting point is the requests table. Each request belongs to one 
patient.

2) Each patient can have one or many requests.
3) Each patient can have none, one or more than one patient/case number. 
Patient/case numbers are not unique to patients - eg 2 patients in two 
separate hospitals could have the same patient number.

4) The request can come from a hospital or a GP.
5) GP's belong to GP practices.
6) Each GP belongs to one GP practice, and each GP practice can have one 
or many GP's.
7) Clinicians belong to hospitals. Each clinician can belong to one or 
more hospitals.
8) Each clinician can have one or more specialities (though only 1 
speciality per hospital).


From this requirement I have constructed the following 'primary' data 
tables:

  requests: id, date, request_number, patient_id, specimen, timestamp
  patients: id, last_name, first_name, dob, nhs_number, timestamp
  patient_numbers: id, patient_id, case_number
  hospitals: id, location_name, organisation_code
  clinicians: id, national_code, surname, initials
  specialities: id, speciality
  gp_practices: id, national_code, address, post_code
  general_practitioners: id, national_code, surname, practice_id

And various link tables:
  clinician_organisations: clinician_id, organisation, speciality_id
  hospital_requests: request_id, clinician_id, hospital_id, timestamp
  gp_requests: request_id, gp_id, practice_id, timestamp
  request_patient_numbers: request_id, patient_number_id

The primary to foreign key relationships are as follows:

requests.patient_id   = patients.id
hospital_requests.request_id  = requests.id
hospital_requests.hospital_id = hospitals.id
hospital_requests.clinician_id= clinicians.id
gp_requests.request_id= requests.id
gp_requests.gp_id = general_practitioners.id
gp_requests.practice_id   = gp_practices.id
clinician_organisations.clinician_id  = clinicians.id
clinician_organisations.speciality_id = specialities.id
patient_numbers.patient_id= patients.id
request_patient_numbers.request_id= requests.id
request_patient_numbers.patient_number_id = patient_numbers.id

The sql to retrieve a request is as follows:

SELECT
  patients.id,
  requests.request_number,
  requests.date,
  patients.last_name,
  patients.first_name,
  patients.dob,
  patients.nhs_number,
  patient_numbers.case_number,
  requests.specimen,
  hospitals.location_name,
  clinicians.surname,
  specialities.speciality
FROM requests
  JOIN patients ON (
patients.id = requests.patient_id
  )
  LEFT JOIN hospital_requests ON (
hospital_requests.request_id = requests.id
  )
  LEFT JOIN hospitals ON (
hospitals.id = hospital_requests.hospital_id
  )
  LEFT JOIN patient_numbers ON (
patient_numbers.patient_id = patients.id
  )
  LEFT JOIN request_patient_numbers ON (
request_patient_numbers.patient_number_id = patient_numbers.id
  AND
request_patient_numbers.request_id = requests.id
  )
  LEFT JOIN clinicians ON (
hospital_requests.clinician_id = clinicians.id
  )
  LEFT JOIN clinician_organisations ON (
clinician_organisations.clinician_id = clinicians.id
  AND
left(clinician_organisations.organisation, 3) =
  left(hospitals.organisation_code, 3)
  )
  LEFT JOIN specialities ON (
specialities.id = clinician_organisations.speciality_id
  )
WHERE requests.id = unique requests.id

This is for a single request from a hospital - I haven't got as far as 
including GP's in the query yet.


The problem is the case number (synonym for patient number) - if a 
patient has 2 or more cases numbers registered to them we get multiple 
records for the same request (one for each additional case number). 
Otherwise, if a patient has 0 or 1 case numbers registered to them then 
we correctly get a single result returned.


This suggests the data model is incorrect, at least where patients and 
case numbers are concerned. I suspect the problem involves the modelled 
relationships between patient_number, patient and request, but I would 
be most grateful for any assistance in optimising the data model so that 
it returns the correct information.

--
Richard Jones


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with db design

[Richard Jones]

Hi,

I have a complex legacy application with around 30 tables which is in 
need of re-factoring, as there are tables with lots of nulls. I've 
partially managed to achieve this so that my queries mostly return 
correct information. But there are some circumstances where duplicate 
data is returned, suggesting my data model is not quite correct.


I haven't included either db schema or queries here due to the likely 
length of the posting, but would initially like to ask if this is the 
right forum to request help with MySQL database design, or is there an 
alternative forum. It is of course not a MySQL-specific question, and 
would be applicable to any relational db. Thanks.

--
Richard Jones


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Migrate HUGE Database

[Richard Heyes]

How very inconsistent and obnoxious.


But yet far more secure. FWIW, if you're transferring between machines 
you can gzip the output of mysqldump to compress it, resulting in far 
less transfer time.


Eg.

mysqldump -u username -p database_name | gzip -c  dump.sql.gz

IIRC

--
Richard Heyes
Employ me:
http://www.phpguru.org/cv

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [PHP] Importing and exporting from MySQL, escape slash problem

[Richard Lynch]

It's possible that there is an .htaccess file in phpMyAdmin that has
Magic Quotes on that is messing you up...

Other than that, it's specific to phpMyAdmin, so maybe ask those guys
what they did...

On Sat, March 1, 2008 7:38 pm, Dave M G wrote:
 PHP List, MySQL List

 In my PHP environment, I have Magic Quotes turned off, and I use the
 mysql_real_escape_string() function clean strings of SQL syntax before
 inserting them into my database.

 So the data stored in my database does not have escape characters in
 it.
 Particularly, double and single quotes don't have slashes in front of
 them.

 This seems to work fine so long as I'm reading data into and out of
 the
 database from within my scripts.

 However, when I backup and import databases - I use the phpMyAdmin
 interface - they have escape slashes in front of every double and
 single
 quote characters. I'm not sure if it's on the export or import where
 they get added in.

 I've looked through the phpMyAdmin online documentation, and I can't
 see
 any option to control the presence of escape slashes. It seems to me
 that if it adds them in when exporting, it should take them out when
 importing. Or vice versa, but in either case be consistent.

 I just want my database to be exactly as it is before any export or
 import options.

 I'm a little muddled as to where I'm making the mistake. Can anyone
 advice on the best practice for preserving my database as is when
 backing up and restoring?

 Thanks for any advice.

 --
 Dave M G

 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
Some people have a gift link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/from/lynch
Yeah, I get a buck. So?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group a select * and a select COUNT from 2 different [... ] (solved thankyou !)

[Richard]

Hello, thankyou to everyone who has helped me out on this one as I did 
not think it was actuallay possible ! :)


This is what worked best for me :

SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 
0) AS count

FROM user_list a
LEFT JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

I'm sorry if I was not clear with my first email yesterday making it 
sound like I wanted a 0 or a 1 and nothing else... I made a mistake and 
thought that I was getting nothing or 1 whereas it was actually counting 
corectly.


I was also suggested a LEFT OUTER JOIN but have read that it is a 
synonym to LEFT JOIN, is this the case or is there a difference between 
the two?


Thanks again,

Richard

David Schneider-Joseph a écrit :

Try this one:

SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 
0) AS count

FROM user_list a
LEFT JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

The LEFT JOIN will ensure you still get a result row even if there are 
no matching rows in `login_table`.  And the COALESCE will give you a 
value of 0 instead of NULL for the count, in that case.


On Feb 19, 2008, at 5:29 PM, Richard wrote:

Sorry it's me again, I made a mistake, it counts the number of logins 
correctly, but does not show members with 0 logins !


Any idea how to do this?

Thanks :)

Peter Brawley a écrit :

Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
count

FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;
PB
-
Richard wrote:

Hello,

This time I'm rearly not sure if this is possible to do. I've got 
two queries that I would like to bring together to make only one 
query ...


I've got a list of users

And also a login table

I would like to list all users and show the number of times they 
have logged in.


So to get the list of users I would do :

SELECT username, first_name, last_name FROM user_list

And to count the number of connections I would do

SELECT COUNT(*) AS count FROM login_table WHERE username = 
$result['username']


Can I do something like this :

SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
count FROM login_table b WHERE a.username = b.username) FROM 
user_list a


I know that the above query can not work but It's just to give a 
better idea about what I'm trying to do . :)


If I do a join, I will the username repeated for each login.

Thanks in advance,

Richard




--
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]

group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

[Richard]

Hello,

This time I'm rearly not sure if this is possible to do. I've got two 
queries that I would like to bring together to make only one query ...


I've got a list of users

And also a login table

I would like to list all users and show the number of times they have 
logged in.


So to get the list of users I would do :

SELECT username, first_name, last_name FROM user_list

And to count the number of connections I would do

SELECT COUNT(*) AS count FROM login_table WHERE username = 
$result['username']


Can I do something like this :

SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count 
FROM login_table b WHERE a.username = b.username) FROM user_list a


I know that the above query can not work but It's just to give a better 
idea about what I'm trying to do . :)


If I do a join, I will the username repeated for each login.

Thanks in advance,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

[Richard]

Hi, and thankyou for trying to help me out! I've tried this and it does 
not work. Here are the problems :


1) If a user has never logged in he doesn't show the user in the list
2) It doesn't count if it is  0 it's not on the liste and if the user 
has logged in more than once the result is 1 (because of the group by ...).


Thankyou

Peter Brawley a écrit :

Richard,

 Can I do something like this :
 SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
 FROM login_table b WHERE a.username = b.username) FROM user_list a

Try ...

SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

PB

-

Richard wrote:

Hello,

This time I'm rearly not sure if this is possible to do. I've got two 
queries that I would like to bring together to make only one query ...


I've got a list of users

And also a login table

I would like to list all users and show the number of times they have 
logged in.


So to get the list of users I would do :

SELECT username, first_name, last_name FROM user_list

And to count the number of connections I would do

SELECT COUNT(*) AS count FROM login_table WHERE username = 
$result['username']


Can I do something like this :

SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
count FROM login_table b WHERE a.username = b.username) FROM user_list a


I know that the above query can not work but It's just to give a 
better idea about what I'm trying to do . :)


If I do a join, I will the username repeated for each login.

Thanks in advance,

Richard






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?

[Richard]

Sorry it's me again, I made a mistake, it counts the number of logins 
correctly, but does not show members with 0 logins !


Any idea how to do this?

Thanks :)

Peter Brawley a écrit :

Richard,

 Can I do something like this :
 SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
 FROM login_table b WHERE a.username = b.username) FROM user_list a

Try ...

SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name,a.lastname;

PB

-

Richard wrote:

Hello,

This time I'm rearly not sure if this is possible to do. I've got two 
queries that I would like to bring together to make only one query ...


I've got a list of users

And also a login table

I would like to list all users and show the number of times they have 
logged in.


So to get the list of users I would do :

SELECT username, first_name, last_name FROM user_list

And to count the number of connections I would do

SELECT COUNT(*) AS count FROM login_table WHERE username = 
$result['username']


Can I do something like this :

SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS 
count FROM login_table b WHERE a.username = b.username) FROM user_list a


I know that the above query can not work but It's just to give a 
better idea about what I'm trying to do . :)


If I do a join, I will the username repeated for each login.

Thanks in advance,

Richard






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup table structure, not data

[Richard Heyes]

Is there any way to backup a complete database structure
(tables/fields/indexes/etc), without the data?  Or even get a creation
script per table?

At present the only way I can think of is to restore a backup to another
server and just delete records (a legacy database with data hitting over
12GB, might take some time) - but there's gotta be an easier way to do
it...


mysqldump has a --no-data option. Try man mysqldump (assuming your 
MySQL server is Unix based.


--
Richard Heyes
http://www.websupportsolutions.co.uk

Knowledge Base and Helpdesk software hosted for you - no
installation, no maintenance, new features automatic and free

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

insert new records from other tables

[Richard]

Hello,

I need to insert one entry(one line) containing 10 values, some from a 
form(ok) but some from two other tables, what is the best way to do this ?


Say for example I need to enter this information :

first_name - last_name - age - sexe - username - email - favorite_colour 
- hobby - inscription_date - timestamp



I already have a table containing : firstname - last_name - sexe - age - 
username

And another one containing: username - email - inscription_date

And I get from the member : favorite_colour - hobby

Out of these three sources I would like to insert into one table 
containing all of these details.


I need this to make a print of one moment So I would be able to pull out 
an entry saying : At this date the information was the following :


first_name - last_name - age - sexe : height - username - email - 
favorite_colour - hobby - inscription_date - timestamp



Can I do something like this :

INSERT INTO info_stamp (key , fav_colour, hobby, stamp_date, firstname, 
last_name, sexe, age, username, email, insc_date )VALUES ('', 
$fav_colour, $hobby, $time, (SELECT  a.firstname, a.last_name, a.sexe, 
a.age, a.username, b.email, b.inscription_date FROM mem_info a JOIN 
mem_login b ON a.username = b.username WHERE a.username = $username));


I guess this query would not actually work, what would be the corect way 
to do this ?


Thanks in advance :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert new records from other tables [ solved, thankyou :) ]

[Richard]

Thanks it works great ! :)


Dan Buettner wrote :

Richard, it's possible,  your syntax is pretty close.

Try this:

INSERT INTO info_stamp
(fav_colour, hobby, stamp_date, firstname,
last_name, sexe, age, username, email, insc_date)
SELECT  $fav_colour, $hobby, $time, a.firstname, a.last_name, a.sexe,
a.age, a.username, b.email, b.inscription_date
FROM mem_info a
JOIN mem_login b ON a.username = b.username
WHERE a.username = $username;

I removed the reference to the 'key' column, fyi.

More info here: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Hope this helps!

-Dan



On Feb 17, 2008 9:09 PM, Richard [EMAIL PROTECTED] wrote:


Hello,

I need to insert one entry(one line) containing 10 values, some from a
form(ok) but some from two other tables, what is the best way to do this ?

Say for example I need to enter this information :

first_name - last_name - age - sexe - username - email - favorite_colour
- hobby - inscription_date - timestamp


I already have a table containing : firstname - last_name - sexe - age -
username
And another one containing: username - email - inscription_date

And I get from the member : favorite_colour - hobby

Out of these three sources I would like to insert into one table
containing all of these details.

I need this to make a print of one moment So I would be able to pull out
an entry saying : At this date the information was the following :

first_name - last_name - age - sexe : height - username - email -
favorite_colour - hobby - inscription_date - timestamp


Can I do something like this :

INSERT INTO info_stamp (key , fav_colour, hobby, stamp_date, firstname,
last_name, sexe, age, username, email, insc_date )VALUES ('',
$fav_colour, $hobby, $time, (SELECT  a.firstname, a.last_name, a.sexe,
a.age, a.username, b.email, b.inscription_date FROM mem_info a JOIN
mem_login b ON a.username = b.username WHERE a.username = $username));

I guess this query would not actually work, what would be the corect way
to do this ?

Thanks in advance :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select unique ? (solved thankyou :))

[Richard]

Price, Randall a écrit :

Since both of these work, I was wondering which one would be faster.

Here is an EXPLAIN on a similar test I did on one of my test tables.

(NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query
window)


SELECT COUNT(*) FROM tblClients
(1660 row(s) returned)
(0 ms taken)

RESET QUERY CACHE

SELECT DISTINCT field1 FROM tblClients
(130 row(s) returned)
(0 ms taken)


EXPLAIN SELECT DISTINCT field1 FROM tblClients
/* 1457 rows, Using temporary */


RESET QUERY CACHE

SELECT field1 FROM tblClients GROUP BY field1
(130 row(s) returned)
(16 ms taken)


EXPLAIN SELECT field1 FROM tblClients GROUP BY field1
/* 1457 rows, Using temporary; Using filesort */


It appears that the SELECT DISTINCT did not have to use the filesort.
So that should be faster, which confirms what I see here.

This is just my $0.02...

Thanks,

Randall Price
 
Secure Enterprise Technology Initiatives

Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA  24060
 


-Original Message-
From: Ben Clewett [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 14, 2008 11:57 AM

To: Richard
Cc: mysql@lists.mysql.com
Subject: Re: select unique ?

Try:

SELECT DISTINCT Colour FROM table;

Or, if you want to do it correctly:

SELECT Colour FROM table GROUP BY color;

Richard wrote:

Hello,

I don't know if it is possible to do this with mysql alone ...
Here goes :
I've got a database list which is like to following :

Num|Name|Colour
---
1|Harry|Red
2|Tom|Blue
3|Jane|Green
4|Philip|Red
5|Sarah|Red
6|Robert|Blue


And from this table I wish to get a list of used colours.

The correct answer would be :

Colour
-
Red
Blue
Green

The answer I don't want :

Colour

Red
Blue
Green
Red
Red
Blue

How would I achieve the first result with mysql ? Is it possible?


Thanks in advance,

Richard





Thanks :) It works great with the SELECT DISTINCT, and if it's faster 
than I will keep to this solution :)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

select unique ?

[Richard]

Hello,

I don't know if it is possible to do this with mysql alone ...
Here goes :
I've got a database list which is like to following :

Num |   Name|   Colour
---
1   |   Harry   |   Red
2   |   Tom |   Blue
3   |   Jane|   Green
4   |   Philip  |   Red
5   |   Sarah   |   Red
6   |   Robert  |   Blue


And from this table I wish to get a list of used colours.

The correct answer would be :

Colour
-
Red
Blue
Green

The answer I don't want :

Colour

Red
Blue
Green
Red
Red
Blue

How would I achieve the first result with mysql ? Is it possible?


Thanks in advance,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: memory usage - mysql tuning!!

[Richard]

bruce a écrit :

Hi..

Fairly new to mysql, in particular tuning.

I have a test mysql db, on a test server. I've got a test app that runs on
multiple servers, with each test app, firing/accessing data from the central
db server.

the central server is on a 2GHz, 1GMem, 100G system. MySQL is the basic app.
the remote/test apps are doing basic selects/inserts, with a few basic
select.. group/order by.

the db schema appears to be pretty straight forward, with primary/unique
fields. keep in mind, i'm not a dba!!!

the my.cnf file is pretty basic. there has been a modification for the
key_buffer_table entry...

my issue, is that when i examine the central mysql (show processlist) i see
a number of connections (~10) with the majority being in a sleep status..
However, when i then check the OS, using top, i see that mysql is running,
consuming ~ 80-90% of the cpu cycles...

so, i'm trying to figure out how to diagnose/solve this issue.

any pointers, comments, suggestions will be greatly appreciated.

this instance of mysql, is 5.x, and is running on a virtual rhel5 os, under
vmware...

thanks
  
Hi, if mysql is the only program running on your test server it's normal 
that it's using 80-90% of the used cpu cycles ... Is it using 80% of the 
total CPU cycles or juste 80% of the used Cpu cyles? If your Cpu is 
running at 0.05 and mysql at 80% it means that mysql is just using 4% of 
the system's CPU, mysql has to listen to new incomming queries even when 
there are none so it's normal that it uses up some CPU ...


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Looking for a more efficient way to achieve the same result

[Richard]

Richard a écrit :
Hello, I'm in the process of programming a customer area with a list 
of subscriptions :


Reference | Title | Type | Date of first subscription | Expires

Each item in this list will have a link to it's details with will show 
a list like this :


Subscribed on : date of first subscription
Renewed on : date of first renewal
Renewed on : date of second renewal
Renewed on : date of third renewal
Expires on : date when expires.

At the moment I have got three mysql tables :
--
1) Products

Reference | Title | Type

2) Subscriptions

number(autoincrement) | reference | date_begin

3) subscriptions details

number(autoincrement) | subscription_number | length(number of months)
--

To get the first table I would :

List subscriptions
For each subscription get list of lengths which I would add together 
and then calculate expire date by :

Total lengths + date_begin

Which gives me the expire date.

However all this seems alot of queries and resources for such a small 
list and as I have not started the programming yet and have not 
created the tables either, I thought I might ask your advice to see if 
you think this is the best way, or if you could think of a better way 
of achieving the same result. Maybe there is a way to get the expire 
date with mysql, or maybe I should rethink my tables?


I hope that everything is clear, and thanks in advance :)


Hi I might have found a better solution.
I could have one table for the latest details and another containing the 
history.
I would still have two tables, but only one query when a customer wishes 
to view his or her subscriptions and only one query when he or her views 
the detail.
What would the best way to copy an entry from one table to another and 
then change the entry value ? What is the best way to do this using a 
minimum of queries?
To copy an entry from one table to another do you have to read the value 
with one query and then insert the value to the other table with a 
second query or does mysql (5.0) have a function to copy data from one 
table to another.


Thanks in advance.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Looking for a more efficient way to achieve the same result - Found solution but would still like some advice :)

[Richard]

Richard a écrit :

Richard a écrit :
Hello, I'm in the process of programming a customer area with a list 
of subscriptions :


Reference | Title | Type | Date of first subscription | Expires

Each item in this list will have a link to it's details with will 
show a list like this :


Subscribed on : date of first subscription
Renewed on : date of first renewal
Renewed on : date of second renewal
Renewed on : date of third renewal
Expires on : date when expires.

At the moment I have got three mysql tables :
--
1) Products

Reference | Title | Type

2) Subscriptions

number(autoincrement) | reference | date_begin

3) subscriptions details

number(autoincrement) | subscription_number | length(number of months)
--

To get the first table I would :

List subscriptions
For each subscription get list of lengths which I would add 
together and then calculate expire date by :

Total lengths + date_begin

Which gives me the expire date.

However all this seems alot of queries and resources for such a small 
list and as I have not started the programming yet and have not 
created the tables either, I thought I might ask your advice to see 
if you think this is the best way, or if you could think of a better 
way of achieving the same result. Maybe there is a way to get the 
expire date with mysql, or maybe I should rethink my tables?


I hope that everything is clear, and thanks in advance :)


Hi I might have found a better solution.
I could have one table for the latest details and another containing 
the history.
I would still have two tables, but only one query when a customer 
wishes to view his or her subscriptions and only one query when he or 
her views the detail.
What would the best way to copy an entry from one table to another and 
then change the entry value ? What is the best way to do this using a 
minimum of queries?
To copy an entry from one table to another do you have to read the 
value with one query and then insert the value to the other table with 
a second query or does mysql (5.0) have a function to copy data from 
one table to another.


Thanks in advance.


Hi me again, just to say I think I've found the solution :

INSERT INTO TABLE2 SELECT * FROM TABLE1

Do I have to do two queries :

INSERT INTO SUB_HISTORY SELECT * FROM SUB WHERE num = '$subnumber';

and 


UPDATE SUB SET end_date = '$newdate' WHERE num= '$subnumber';

Or is it possible to do both queries in one ?

Thanks :)




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Looking for a more efficient way to achieve the same result.

[Richard]

Hello, I'm in the process of programming a customer area with a list of 
subscriptions :


Reference | Title | Type | Date of first subscription | Expires

Each item in this list will have a link to it's details with will show a 
list like this :


Subscribed on : date of first subscription
Renewed on : date of first renewal
Renewed on : date of second renewal
Renewed on : date of third renewal
Expires on : date when expires.

At the moment I have got three mysql tables :
--
1) Products

Reference | Title | Type

2) Subscriptions

number(autoincrement) | reference | date_begin

3) subscriptions details

number(autoincrement) | subscription_number | length(number of months)
--

To get the first table I would :

List subscriptions
For each subscription get list of lengths which I would add together 
and then calculate expire date by :

Total lengths + date_begin

Which gives me the expire date.

However all this seems alot of queries and resources for such a small 
list and as I have not started the programming yet and have not created 
the tables either, I thought I might ask your advice to see if you think 
this is the best way, or if you could think of a better way of achieving 
the same result. Maybe there is a way to get the expire date with mysql, 
or maybe I should rethink my tables?


I hope that everything is clear, and thanks in advance :)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: failed queries

[Richard Heyes]

John Roddy wrote:

I know that successful, data changing queries get logged in the binary log. But 
is there any
 way to see the actual queries that fail (i.e. timeouts, deadlocks)? 
I'm hoping there's a
 way other than using the general query log, which takes up too much 
space just to catch

 that occasional failed update.

Use your abstraction layer to log queries that pass through it.

--
Richard Heyes
http://www.websupportsolutions.co.uk

Knowledge Base and Helpdesk software that eases your support
burden and helps increase your sales.

** NOW OFFERING FREE ACCOUNTS TO CHARITIES AND NON-PROFITS **

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Bigint

[Richard Heyes]

Performance wise, what is a BIGINT like in comparison to an INT on a 32 
bit machine?


Thanks.

--
Richard Heyes
http://www.websupportsolutions.co.uk

Mailing list management service allowing you to reach your Customers
and increase your sales.

** NOW OFFERING FREE ACCOUNTS TO CHARITIES AND NON-PROFITS **

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bigint

[Richard Heyes]

I think it's the same.


I seem to remember that a BIGINT id two INTs tacked together, so how is 
that possible?


--
Richard Heyes
http://www.websupportsolutions.co.uk

Mailing list management service allowing you to reach your Customers
and increase your sales.

** NOW OFFERING FREE ACCOUNTS TO CHARITIES AND NON-PROFITS **

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SPAM] - Re: OT: Sun to buy Mysql - Email found in subject

[Richard Heyes]

Will this bring good things to MySQL?


$800,000,000 tends to bring good things. Hopefully. :-)

--
Richard Heyes
http://www.websupportsolutions.co.uk

Mailing list management service allowing you to reach your Customers
and increase your sales.

** NOW OFFERING FREE ACCOUNTS TO CHARITIES AND NON-PROFITS **

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help with query, (question simplified as last mail was very complicated to understand :))

[Richard]

Richard a écrit :

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


As I have had no answer I presume that what I want to do is not possible 
or my question is not well explained. Anyhow I've rethought the system 
so I do not need to keep members information and now instead of adding a 
new entry I will now just change the existing one. I won't keep old 
members information in the database but I'll still have the database 
daily backups if I need the old information.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help with a query...

[Richard]

Hello, I'm trying to get what is for me quite a complicated query to 
work, if it's possible to do so anyway ...

Here is my old query :
SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN 
info b ON a.from=b.code WHERE a.id='28'
It worked fine untill I needed to have more than one 'code' in the info 
table ...

To get data from the info table I do this :
SELECT * FROM info WHERE code LIKE $code ORDER BY num DESC LIMIT 1
And the reason for this is I allow members to change their data, but I 
need to keep their old data.
Because there are for example 3 lines with the same code in the messages 
table the messages are repeated 3 times.

My question is, is there a way to limit the answer to once ?
In otherwords this is what I want to do :
SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN 
info b ON a.from=(b.code ORDER BY b.num DESC LIMIT 1) WHERE a.id='28'

Do you understand what I mean? I know it's not very clear ... :)

Well here goes again just incase :
I've got two tables. one called messages and the other called info.
The messages table contains :

id = autoincrement key
from = members code who sent message
date = date when message was sent

For each message I need to get the firstname, surname and title which 
are stored in the info table.

The info table contains :

num = autoincrement key
code = member code
name = firstname
surname =  surname
title = Sir, Miss or Mrs

However each time a member changes his/her info it creates a new line 
instead of changing the old one. This is so I can keep track of what 
their old info was, a bit like the system a wiki uses.

So for example I could have 3 lines with the member code 'm00025'.
This is why I would need to be able to combine : SELECT 
a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN info b ON 
a.from=b.code WHERE a.id='28'

and ORDER BYnum DESC LIMIT 1

I hope I've been clear enough ... thanks in advance :)

Richard







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with query, (question simplified as last mail was very complicated to understand :))

[Richard]

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on code table1 = code table3 where messageid = for 
example 28


table 1 contains :

 message   from   messageid
--
message1  |  code1  |28
message2  |  code1  |28
message3  |  code1  |28
message4  |  code1  |29


table 2 contains

name |  code  |  num
--
name1  |  code2  |  1
name2  |  code1  |  2
name3  |  code1  |  3

If I do :
SELECT a.message,,b.name  FROM table1 a JOIN table2 b ON a.code=b.code 
WHERE a.id='28'


I get :
message|  name
---
message1  |  name2
message2  |  name2
message3  |  name2
message1  |  name3
message2  |  name3
message3  |  name3


But all I want to get is :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name |  code  |  num
--
name3  |  code1  |  3

I now need to somehow combine the two to get :

message|  name
---
message1  |  name3
message2  |  name3
message3  |  name3

Of course I have simplified everything down to the minimum :)

Thanks in advance,

Richard


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Determining Table Storage Engine Type on Crashed Table

[Richard Edward Horner]

FYI, this did not work :)

Thanks though!

Rich(ard)

On Nov 23, 2007 3:37 AM, Paul McCullagh [EMAIL PROTECTED] wrote:
 Maybe this will work:

 SHOW CREATE TABLE table_name;


 On Nov 21, 2007, at 9:42 PM, Richard Edward Horner wrote:

  Hey everybody,
 
  Hopefully some of you are already enjoying time off. I am not...yet :)
 
  Anyway, is there a way to determine what storage engine a table is
  using if it's crashed?  When it's fine, I can just run:
 
  mysql show table status like 'table_name';
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  | Name| Engine | Version | Row_format | Rows   |
  Avg_row_length | Data_length | Max_data_length  | Index_length |
  Data_free | Auto_increment | Create_time | Update_time
  | Check_time  | Collation | Checksum | Create_options
  | Comment |
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  | table_name | MyISAM |  10 | Fixed  | 985984 | 13
  |12817792 | 3659174697238527 | 34238464 | 0 |
  1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21
  15:28:18 | latin1_swedish_ci | NULL || |
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  1 row in set (0.00 sec)
 
  As you can see, the second column returned is the Engine. In this
  case, MyISAM. Now, if I crash the table, it doesn't work:
 
  mysql show table status like 'table_name';
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  | Name| Engine | Version | Row_format | Rows | Avg_row_length
  | Data_length | Max_data_length | Index_length | Data_free |
  Auto_increment | Create_time | Update_time | Check_time | Collation |
  Checksum | Create_options | Comment
  |
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  | table_name | NULL   |NULL | NULL   | NULL |   NULL |
 NULL |NULL | NULL |  NULL |
  NULL | NULL| NULL| NULL   | NULL  | NULL |
  NULL   | Table './blah/table_name' is marked as crashed and
  should be repaired |
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  1 row in set (0.00 sec)
 
  Now, let's assume for a moment this were an InnoDB table. If I were to
  try and run repair, it would say that the storage engine does not
  support repair so clearly it knows what the storage engine is. How do
  I get it to tell me? Or I guess a broader more helpful question would
  be, What are all the ways to determine a table's storage engine
  type?
 
  Thanks,
  --
  Richard Edward Horner
  Engineer / Composer / Electric Guitar Virtuoso
  [EMAIL PROTECTED]
  http://richhorner.com - updated June 28th
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 





-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Determining Table Storage Engine Type on Crashed Table

[Richard Edward Horner]

Good call, Paul. I'll try it out!

Thanks, Rich(ard)

On Nov 23, 2007 3:37 AM, Paul McCullagh [EMAIL PROTECTED] wrote:
 Maybe this will work:

 SHOW CREATE TABLE table_name;


 On Nov 21, 2007, at 9:42 PM, Richard Edward Horner wrote:

  Hey everybody,
 
  Hopefully some of you are already enjoying time off. I am not...yet :)
 
  Anyway, is there a way to determine what storage engine a table is
  using if it's crashed?  When it's fine, I can just run:
 
  mysql show table status like 'table_name';
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  | Name| Engine | Version | Row_format | Rows   |
  Avg_row_length | Data_length | Max_data_length  | Index_length |
  Data_free | Auto_increment | Create_time | Update_time
  | Check_time  | Collation | Checksum | Create_options
  | Comment |
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  | table_name | MyISAM |  10 | Fixed  | 985984 | 13
  |12817792 | 3659174697238527 | 34238464 | 0 |
  1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21
  15:28:18 | latin1_swedish_ci | NULL || |
  +-++-++
  ++-+--+--
  +---++-
  +-+-+---
  +--++-+
  1 row in set (0.00 sec)
 
  As you can see, the second column returned is the Engine. In this
  case, MyISAM. Now, if I crash the table, it doesn't work:
 
  mysql show table status like 'table_name';
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  | Name| Engine | Version | Row_format | Rows | Avg_row_length
  | Data_length | Max_data_length | Index_length | Data_free |
  Auto_increment | Create_time | Update_time | Check_time | Collation |
  Checksum | Create_options | Comment
  |
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  | table_name | NULL   |NULL | NULL   | NULL |   NULL |
 NULL |NULL | NULL |  NULL |
  NULL | NULL| NULL| NULL   | NULL  | NULL |
  NULL   | Table './blah/table_name' is marked as crashed and
  should be repaired |
  +-++-++--
  ++-+-+--
  +---++-+-
  ++---+--+
  +-
  ---+
  1 row in set (0.00 sec)
 
  Now, let's assume for a moment this were an InnoDB table. If I were to
  try and run repair, it would say that the storage engine does not
  support repair so clearly it knows what the storage engine is. How do
  I get it to tell me? Or I guess a broader more helpful question would
  be, What are all the ways to determine a table's storage engine
  type?
 
  Thanks,
  --
  Richard Edward Horner
  Engineer / Composer / Electric Guitar Virtuoso
  [EMAIL PROTECTED]
  http://richhorner.com - updated June 28th
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 





-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Determining Table Storage Engine Type on Crashed Table

[Richard Edward Horner]

Micah,

I don't think this will work in all cases. Both a memory table and a
blackhole table only have an .frm file. Admittedly, we can ignore
blackhole table for practical purposes. But, while we're discussing
practical purposes, an InnoDB table's data is in the main InnoDB
storage file unless you use innodb_file_per_table which I've actually
never seen any of my clients use in any deployment I've worked on.

So, this can work with either some configuration or some prior
knowledge of the scheme (i.e. no memory tables) but I don't think it's
a be all end all or is there more that you know that I don't?

Thanks, Rich(ard)

On Nov 22, 2007 12:43 PM, Micah Stevens [EMAIL PROTECTED] wrote:
 Look at the data files. The extension of the file will tell you.



 On 11/21/2007 12:42 PM, Richard Edward Horner wrote:
  Hey everybody,
 
  Hopefully some of you are already enjoying time off. I am not...yet :)
 
  Anyway, is there a way to determine what storage engine a table is
  using if it's crashed?  When it's fine, I can just run:
 
  mysql show table status like 'table_name';
  +-++-++++-+--+--+---++-+-+-+---+--++-+
  | Name| Engine | Version | Row_format | Rows   |
  Avg_row_length | Data_length | Max_data_length  | Index_length |
  Data_free | Auto_increment | Create_time | Update_time
  | Check_time  | Collation | Checksum | Create_options
  | Comment |
  +-++-++++-+--+--+---++-+-+-+---+--++-+
  | table_name | MyISAM |  10 | Fixed  | 985984 | 13
  |12817792 | 3659174697238527 | 34238464 | 0 |
  1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21
  15:28:18 | latin1_swedish_ci | NULL || |
  +-++-++++-+--+--+---++-+-+-+---+--++-+
  1 row in set (0.00 sec)
 
  As you can see, the second column returned is the Engine. In this
  case, MyISAM. Now, if I crash the table, it doesn't work:
 
  mysql show table status like 'table_name';
  +-++-++--++-+-+--+---++-+-++---+--+++
  | Name| Engine | Version | Row_format | Rows | Avg_row_length
  | Data_length | Max_data_length | Index_length | Data_free |
  Auto_increment | Create_time | Update_time | Check_time | Collation |
  Checksum | Create_options | Comment
  |
  +-++-++--++-+-+--+---++-+-++---+--+++
  | table_name | NULL   |NULL | NULL   | NULL |   NULL |
 NULL |NULL | NULL |  NULL |
  NULL | NULL| NULL| NULL   | NULL  | NULL |
  NULL   | Table './blah/table_name' is marked as crashed and
  should be repaired |
  +-++-++--++-+-+--+---++-+-++---+--+++
  1 row in set (0.00 sec)
 
  Now, let's assume for a moment this were an InnoDB table. If I were to
  try and run repair, it would say that the storage engine does not
  support repair so clearly it knows what the storage engine is. How do
  I get it to tell me? Or I guess a broader more helpful question would
  be, What are all the ways to determine a table's storage engine
  type?
 
  Thanks,
 




-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Determining Table Storage Engine Type on Crashed Table

[Richard Edward Horner]

Hey everybody,

Hopefully some of you are already enjoying time off. I am not...yet :)

Anyway, is there a way to determine what storage engine a table is
using if it's crashed?  When it's fine, I can just run:

mysql show table status like 'table_name';
+-++-++++-+--+--+---++-+-+-+---+--++-+
| Name| Engine | Version | Row_format | Rows   |
Avg_row_length | Data_length | Max_data_length  | Index_length |
Data_free | Auto_increment | Create_time | Update_time
| Check_time  | Collation | Checksum | Create_options
| Comment |
+-++-++++-+--+--+---++-+-+-+---+--++-+
| table_name | MyISAM |  10 | Fixed  | 985984 | 13
|12817792 | 3659174697238527 | 34238464 | 0 |
1182153 | 2007-11-15 17:44:28 | 2007-11-21 15:28:07 | 2007-11-21
15:28:18 | latin1_swedish_ci | NULL || |
+-++-++++-+--+--+---++-+-+-+---+--++-+
1 row in set (0.00 sec)

As you can see, the second column returned is the Engine. In this
case, MyISAM. Now, if I crash the table, it doesn't work:

mysql show table status like 'table_name';
+-++-++--++-+-+--+---++-+-++---+--+++
| Name| Engine | Version | Row_format | Rows | Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment
|
+-++-++--++-+-+--+---++-+-++---+--+++
| table_name | NULL   |NULL | NULL   | NULL |   NULL |
   NULL |NULL | NULL |  NULL |
NULL | NULL| NULL| NULL   | NULL  | NULL |
NULL   | Table './blah/table_name' is marked as crashed and
should be repaired |
+-++-++--++-+-+--+---++-+-++---+--+++
1 row in set (0.00 sec)

Now, let's assume for a moment this were an InnoDB table. If I were to
try and run repair, it would say that the storage engine does not
support repair so clearly it knows what the storage engine is. How do
I get it to tell me? Or I guess a broader more helpful question would
be, What are all the ways to determine a table's storage engine
type?

Thanks,
-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb mysql crash

[Richard Edward Horner]

Marten,

Yeah, my experience has been that InnoDB is great when it's working
but a complete nightmare when it stops working. I have scripts to deal
with this which I'm actually hoping to release to the public in the
near future. Essentially, what you need to do is edit your my.cnf to
bring MySQL up with innodb_force_recovery. Then you should run check
table on every table (hence the script part of the equation). For the
tables that report corrupt, you can try to dump their data out
although this never works for me. It always says SELECT failed due to
corrupt key index.

Regardless of whether or not you manage to dump the data out of the
corrupt tables, drop them, stop mysql. Edit the my.cnf to comment out
the innodb_force_recovery statement because you can't write to the
database when in recovery mode and restart the server. Hopefully it
will actually start now that the corrupt tables are gone. Now you can
import your dumps and off you go!

Chances are you will need to do most of this with the --socket flag to
prevent your application from accessing your database while you are
doing this. Make sure you then use the --socket flag on all your dump
and import commands or in the connection string in your check script.

Hope this helps.


On Nov 7, 2007 2:45 AM, Marten Lehmann [EMAIL PROTECTED] wrote:
 Hello,

 today I got this in my logs and mysql stopped working.

 InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
 InnoDB: how to resolve the issue.
 071107  8:46:26  InnoDB: Flushing modified pages from the buffer pool...
 071107  8:46:26  InnoDB: Started; log sequence number 1 1008136481
 /var/mysql/mysql-4.1.18/libexec/mysqld: ready for connections.
 Version: '4.1.18-log'  socket: '/tmp/mysql.sock'  port: 3306  Source
 distribution
 InnoDB: Error: page n:o stored in the page read in is 538976288, should be
 1644!
 071107  8:46:28  InnoDB: Error: page 538976288 log sequence number 538976288
 538976288
 InnoDB: is in the future! Current system log sequence number 1 1008136645.
 InnoDB: Your database may be corrupt.
 InnoDB: Database page corruption on disk or a failed
 InnoDB: file read of page 1644.
 InnoDB: You may have to recover from a backup.
 071107  8:46:28  InnoDB: Page dump in ascii and hex (16384 bytes):
  len 16384; hex
 2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
 071107  8:46:29  InnoDB: Page checksum 138226020, prior-to-4.0.14-form
 checksum 3161919040
 InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum
 538976288
 InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 538976288
 InnoDB: Page number (if stored to page already) 538976288,
 InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 538976288
 InnoDB: Database page corruption on disk or a failed
 InnoDB: file read of page 1644.

 So great, that mysql doesn't even tell me at which table or database to look.

 InnoDB: You may have to recover from a backup.
 InnoDB: It is also possible that your operating
 InnoDB: system has corrupted its own file cache
 InnoDB: and rebooting your computer removes the
 InnoDB: error.
 InnoDB: If the corrupt page is an index page
 InnoDB: you can also try to fix the corruption
 InnoDB: by dumping, dropping, and reimporting
 InnoDB: the corrupt table. You can use CHECK
 InnoDB: TABLE to scan your table for corruption.
 InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
 InnoDB: about forcing recovery.
 InnoDB: Ending processing because of a corrupt database page.

 Number of processes running now: 2
 mysqld process hanging, pid 19729 - killed
 mysqld process hanging, pid 19729 - killed
 071107 08:46:29  mysqld restarted
 071107  8:46:29 [ERROR] Can't start server: Bind on TCP/IP port: Address
 already in use
 071107  8:46:29 [ERROR] Do you already have another mysqld server running on
 port: 3306 ?
 071107  8:46:29 [ERROR] Aborting

 071107  8:46:29 [Note] /var/mysql/mysql-4.1.18/libexec/mysqld: Shutdown
 complete

 071107 08:46:29  mysqld ended


 I cannot count any more how often I had problems with innodb. I regret ever
 compiled it in, but now that a lot of users are using it, I need to recover
 from the problem.

 But there is one major problem: All error messages and recovery documentation
 of innodb sound, as if I would deal with just a few tables. But I have
 hundrets of databases with thousands of tables! So there is no fast dump and
 insert of tables.

 Any ideas (besides from dumping everything and importing at then)?

 Regards
 Marten



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http

Re: mysql connection problems

[Richard Edward Horner]

Ken,

You should probably be specifying the socket for both the startup
commands and the connect commands just to be sure. Without seeing your
config files, it's hard to say much else.

Rich(ard)

On Nov 6, 2007 9:37 PM, tech user [EMAIL PROTECTED] wrote:
 Hello members,

 I have two mysqld run on the same host (redhat linux OS with 2.4 kernel).
 the two mysqld are in different versions, one is 4.0.20,another is 5.0.45.

 the mysql 4.0.20 uses /etc/my.cnf as its config file,listening on default
 3306 port.
 the mysql 5.0.45 uses /etc/mysql5.cnf as its config file,listening on 3307
 port.

 I start them on command line:

 /usr/local/mysql/bin/mysqld_safe   # for mysql4
 /opt/mysql5/bin/mysqld_safe --defaults-file=/etc/mysql5.cnf   # for
 mysql5

 All run fine.I didn't see exceptions in mysql's error logs.

 But, when I try to connect to mysql5, with the command,

 mysql -uroot -P3307 -h127.0.0.1

 Sometime I login it successfully,but most time I can't. The connection
 seems be blocked.

 (I don't run any iptables or firewall on this host).

 This let me really be confused. please help. Thanks!

 --Ken



 National Bingo Night. Play along for the chance to win $10,000 every week. 
 Download your gamecard now at Yahoo!7 TV. 
 http://au.blogs.yahoo.com/national-bingo-night/



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
Richard Edward Horner
Engineer / Composer / Electric Guitar Virtuoso
[EMAIL PROTECTED]
http://richhorner.com - updated June 28th

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]