Re: Mystery error in GRANT statement
> Date: Monday, October 03, 2016 23:18:14 -0700 > From: James Moe > > 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
> 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?
> 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
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?
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
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
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 : > On 2015-09-04 11:39 AM, Richard Reina wrote: > > > 2015-09-04 11:18 GMT-05:00 Peter Brawley : > >> 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
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
Re: Trouble with LEFT JOIN
2015-09-04 11:18 GMT-05:00 Peter Brawley : > 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 );
Trouble with LEFT JOIN
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
When to create a new user?
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
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
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 : > > 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" > Reply-To: "Richard Reina" > Date: 07/29/15 10:19 AM > To: "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
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
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
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 escribió: > > > > > 2014/1/17 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? > > 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
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
To activate log-slave-updates do I just add "log-slave-updates" to the my.cnf file? 2013/4/30, Manuel Arostegui : > 2013/4/30 Richard Reina > >> 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
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 : > 2013/4/30 Richard Reina > >> 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
Query Help
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
Re: Query Help
Perfect! Thank you Larry et all. Have a great weekend. 2013/4/19 Larry Martell > On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina > 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); >
Determing number of queries
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
Re: Please Help. selectcol_arrayref problem
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 > On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina > 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. >
Please Help. selectcol_arrayref problem
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
query help
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?
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
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
Re: query problem with null
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 > 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 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?
query question
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
Re: table design question
Thank you very much for all the insightful advice. I will keep the separated. 2011/9/19 Jerry Schwartz > >-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 > > > >
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? Thanks, Richard
Re: Using @ variables with LIKE,CONCAT
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 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
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 > 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
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
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
On 20 January 2011 19:20, Dotan Cohen wrote: > On Thu, Jan 20, 2011 at 19:21, Richard Quadling 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
On 20 January 2011 16:20, Dotan Cohen wrote: > On Thu, Jan 20, 2011 at 17:00, Richard Quadling 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
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On 20 January 2011 14:32, Dotan Cohen 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
replication question
-- 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.
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
Thank you very much for all the insightful replies. I think I can get it to work with a join. Joerg Bruehe 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
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?
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
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
2010/1/3 Ashley Sheridan : > 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=ZEND002498&r=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
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?
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
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 NOV < wrong 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
Re: column exists but unknown
Nevermind, the problem was not with the DB :\ On Tue, Mar 3, 2009 at 10:44 AM, Richard Whitney 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
column exists but unknown
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: [PHP] Because you guys/gals/girls/women/insert pc term here are a smart lot
>>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
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]
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]
Re: Stopping DNS Lookups
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: Stopping DNS Lookups
[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]
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: Data files from 4.1.13 with 5.0.x
> 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
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]
Re: Table aliasing
> 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: Table aliasing
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]
Table aliasing
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: select count(). Help-a-newb
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]
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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
Re: Help with ORDER BY using two colomns [ solved thankyou :) ]
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
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
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]
Help with ORDER BY using two colomns
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]
Help with a complex data model
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 = 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]
Re: Help with db design
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 db design
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
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
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 !)
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]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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]
group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
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
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]
Re: insert new records from other tables [ solved, thankyou :) ]
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]
insert new records from other tables
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: select unique ? (solved thankyou :))
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 ?
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!!
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 -> Found solution but would still like some advice :)
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]
Re: Looking for a more efficient way to achieve the same result
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]
Looking for a more efficient way to achieve the same result.
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
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]
Re: [SPAM] - Re: OT: Sun to buy Mysql - Email found in subject
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: Bigint
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]
Bigint
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: Help with query, (question simplified as last mail was very complicated to understand :))
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 query, (question simplified as last mail was very complicated to understand :))
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]
help with a query...
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]
Re: Determining Table Storage Engine Type on Crashed Table
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
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
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
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: mysql connection problems
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]
Re: innodb mysql crash
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 > > > > --