FULLTEXT searches with hyphens.
Does anyone know how to get FULLTEXT indexing not to treat hyphens as word breaks? Basically I am searching part numbers and descriptions and need something like Z-5500 to be returned. I want to keep using FT for the scoring. I lowered the min word length to 2. I am thinking I could lower it to 1 and replace the - in the `match against term` with a space, but it would be cleaner if this can be manipulated in the index. Thanks for any suggestions, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Jeffrey G. Ubalde wrote: Good day list! I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built... so if you did that, for every query that alters the data many indexes will have to be written. A lot of extra overhead if they will never be used. Look to see where indexes are needed by the types of queries you are writing. Add a slow query log to my.cnf. This will give you a very good idea of where you might need some indexes. Then trace the queries to make sure the indexes you've created are being used. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE and INDEX using same field.
When creating an index in phpMySQL I get a warning message about having 2 indexes using the same field. The table is simply a product comment table. The first index is simply an INDEX on the product_id to speed up displaying them when someone wants to lookup the comments for that product. The second is a UNIQUE constraint on the product_id and the user_id to make sure a user does not insert more than one comment per product. phpMySQL shows me the following warning: *UNIQUE and INDEX keys should not both be set for column `product_id` *If that really is bad to do... besides programatically how would I do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE and INDEX using same field.
[EMAIL PROTECTED] wrote: Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/28/2005 11:28:42 AM: When creating an index in phpMySQL I get a warning message about having 2 indexes using the same field. The table is simply a product comment table. The first index is simply an INDEX on the product_id to speed up displaying them when someone wants to lookup the comments for that product. The second is a UNIQUE constraint on the product_id and the user_id to make sure a user does not insert more than one comment per product. phpMySQL shows me the following warning: *UNIQUE and INDEX keys should not both be set for column `product_id` *If that really is bad to do... besides programatically how would I do this. Thanks, Mike A UNIQUE *is* an INDEX with an added twist. It's a special kind of INDEX that only allows single combinations of the values in the columns that compose the INDEX. A regular INDEX does not have this restriction. Defining UNIQUE(product_id, user_id) creates an INDEX. Any index that uses more than one column also acts as an index for each left-most sets of columns, including just the first one. Your UNIQUE and your INDEX overlap and the INDEX is not necessary. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks for the answer. I know it is an index... I did not know that an index using 2 columns will be used when calling a statement that uses only one of those columns. Just for the fun of it I will trace a query to see if uses it. Thanks again, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DATE using USA Date Format
I have some data where the date is already in USA format (MM-DD-).
I would like to be able to insert this data without having to massage
the date in the code.
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Thanks,
MIke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DATE using USA Date Format SOLUTION
Dan Nelson wrote:
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Try the STR_TO_DATE function:
SELECT STR_TO_DATE('12-31-2004','%m-%d-%Y');
2004-12-31
Yup.. that works... Thanks.
INSERT INTO `test` (`mydate`) VALUES(STR_TO_DATE('12-31-2004', '%m-%d-%Y'));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need some help
Rhino wrote: - Original Message - From: iNFERNo [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 5:23 PM Subject: I need some help Hi, I have a problem: I am using mysql 4.0.24 and I need to make some reports from a database: mysql describe events; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | user_id | varchar(55) | | | 0 || | dep_id | int(15) | | | 0 || | event_id | int(15) | | | 0 || | year| int(15) | | | 0 || | day | int(15) | | | 0 || | month | int(15) | | | 0 || | ev_status | int(11) | YES | | 0 || | ev_type | int(11) | YES | | 0 || | ev_priority | int(11) | YES | | 0 || +-+--+--+-+-++ I need something to get: | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | user_1 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_2 1COUNT COUNT COUNT COUNT COUNT COUNT COUNT user_3 2COUNT COUNT COUNT COUNT COUNT COUNT COUNT . . . The problem is that the way I am getting this now is with a query for each user and for each ev_status and the output is in PHP. Is there a better way to do this ? I am only a beginner in MySQL and want to learn more and improve. First of all, congratulations on identifying your version of MySQL and giving the definition of your table. That is an excellent start to getting an answer to your question. Far too many people post here without identifying their MySQL version or giving the definition of their tables, making it very difficult to answer their questions without having to ask many followup questions. Am I missing something... but isn't id defined as the primary key? On a different topic.. if you want to make your life easy with PHP. Instead of saving, the year, month, day... personally I always simply use an int and save all dates as Unix Timestamps. As long as you are working more or less in this century, you will be fine. That is a personal choice, from someone that has built MANY calendars. As for selecting all of them like that. I question why.. but SELECT user_id, dep_id, IF(ev_status=1,1,0), IF(ev_status=2,10) ... and so on. But I really don't get why you would be doing that Mike . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create index if not exists????
I'm writing an install script and need to create an index if it does not exists. I've tried SQL statements but none of them work. Basically I'm looking to do something like $sql[] = ALTER TABLE `exp_members` ADD INDEX IF NOT EXIST `idx_members_username` ( `username` ); Is this possible? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation
I just upgraded from 4.0.8 to 4.1.9. On MySql 4.1.9 my default collation seems to be latin1_swedish_ci. I need to support french accents. Right now, not sure if it's MySQL or PHP. But all accented caracters in the database are coming out wrong. I'm thinking I should just use utf8_general_ci but have no idea what the implications are. Also I have no idea why latin1_swedish_ci.was chosen as my default. Does anyone have a good link of where I can read up more on choosing a collation? How to create the default? Or if you have a quick amswer for me that would be great. Right now almost every 3rd word displays wrong. Thanks, Mike
Re: importing data
Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculating User Ranks (SQL Query Question)
Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Wild Card Searches
I've been trying to implement full text searches. Genreally I use either LIKE or REGEXP for searches but wanted to try some Full Text Searches. It's wonderfully fast and is working well for full words, however I have not found a way to add wildcards in a search. For example if in my text fields I have the word residential I would like the key resident to match it. Is there any way to do this in Full Text Searches? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Wild Card Searches
Jim Winstead wrote:
For example:
SELECT record FROM table
WHERE MATCH (record) AGAINST ('resident*' IN BOOLEAN MODE)
(Using 'IN BOOLEAN MODE' requires MySQL 4.0 or later.)
I guess it is time to upgrade from 3.23, I'm using RedHat Linux and have
upgraded on some machines but it is a pain to do since many dependencies
need to be recompiled as well.
Thanks,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. Mike Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing sort order of items.
I'm trying to create the most efficient way to allow a user to change the display order of a group of rows in a table. Lets say the basic table is: id group_id name sort_order The query to display it would be SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order Now when I display it they currenlty all have the same sort_order value so they come in the order however the db finds them. In my PHP app... I have a small arrow that allow them to move a row up or down changing the display order. Currently this is done by looping through the results of all the items in a group and reassigning a new sort_order value to each one. Meaning 1 SELECT and MANY updates (1 for each item in the group). I was wondering if anyone has come up with a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing sort order of items.
It would be equally easy to swap any two items in the list, not just two adjecent items. Just swap sort_order values. Yup... I think that is the key! Basically as they are inserted I will look up the max sort_order value for that group so far and increase that by one for the current insert. Then when it come to changing the sort order values I will simply swap it with the value of the previous or next one. So only 2 updates. The only thing I need to look out for is not to allow them to try and move the first row up... or the last row down. Thanks for the help, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: How to deal with multiple languages
Personally I would keep a table for the translations. Some identifier for what it is, and then and id for the language. So if something doesn't exist in one language you know about it and can default to another language. It also makes it easier to add new languages in my opinion. somthing like: artist_id | 1234 content | bio language | english artist_id | 1234 content | bio language | french artist_id | 1234 content | bio language | german You can index the 3 columns together. Could also simply be 2 field that makes it a bit more vesatile content and language bio_1234 english bio_1234 french description_1234 spanish Ligaya Turmelle wrote: Looks good to me but I'm a relative beginner. Maybe another option - make Spanish, English, and German tables, then link those to the main table. This however can slow you down if you are doing multiple reads of the various tables. Respectfully, Ligaya Turmelle Graham Anderson wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
It's not translating your vars to their respective values. I didn't look to see why... But MySQL doesn't know what $_POST['order'] is. David Blomstrom wrote: This may be a purely PHP problem, but the error message says SQL syntax. Check the manual that corresponds to your MySQL server version... Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') ORDER BY $_POST['order'], $_POST['direction'] - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '['order'], $_POST['direction']' at line 11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045
http://dev.mysql.com/doc/mysql/fr/Access_denied.html Philippe Cabet wrote: Good morning, Im a new Easyphp1-7 user. My computer environnement is Windows XP version 2002 service pack 1. Apache 1.3.27 PHP 4.3.3 PHPMYADMIN 2.5.3 MYSQL 4.0.15 My problem is; With EasyPhp menu, I choise Administator, PhpAdmin, Gestion BDD. Then I tried to set a password on my database and I receive the following message. #1045 - Accs refus pour l'utilisateur: '[EMAIL PROTECTED]' (mot de passe: NON) I read the the Mysql document. Then I set on my config.inc.php the password parameter equal to the password I tried to initiate on my database. Then I trie to goback to Gestion BDD and all time I receive the screen message #1045. Other way, I would like to ignore the password I entered on my database to continue my job. Could you give me the solution? Thanks Bests regard. Philippe Cabet Tl : 06 60 12 01 01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: massive fulltext indexes - what hardware?
Another thing to consider is how many transactions per minute/second you will need to serve. Mark Maunder wrote: I'm busy building an application that will have 10 million records, each with a chunk of text - about 500 words each, on average. Does anyone have any benchmarks they can share with mysql's fulltext search performance on indexes of this size? snip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nice to have you!
I just wanted to say that I think it's great that people like Heikki Tuuri monitor this list and contribute to it. Kudos to you! Heikki Tuuri wrote: snip Best regards, Heikki Tuuri Innobase Oy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: True/False data type
Personally I use a tinyint(1) unsigned. Scott Hamm wrote: What data type should I use to use True/False type when I create a table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: html links inside varchar and text fields
Just put the href into the database. When you output it to html in PHP wrap it wth the rest. $href=$sql-data['href']; echo a href=\ . $href . \The Link/a\n; leegold wrote: I have to put html links inside varchar and text fields. They have to be (somehow) fulltext searchable (of course a substring, %keyword% search, would pick them up - I realize that) and they have to render as clickable links when I output the fields via PHP. How would I do this? Is this more of a PHP level type problem? Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filed names with trailing spaces
I had created a table and started populating it, but did not get to certain fields until later. When I tried to do an insert it would not go. It was not reconizing 2 fields. I read and re-read the statement etc. Tried it from phpMyAdmin, command line and my php script. Anyways, I removed one field and put it back in and it worked... But now I really wanted to figure out why. After another few minutes of invetigation I realized that I had a trailing space in the name. It was due to copy and pasting the names into phpMyAdmin. You would think someone would haver put a trim in there. Well... Just thought I would pass on this experience. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show databases shows all even if no rights;
All priviliges for that user in mysql.user are set to N. I know this is hard to read but here are the outputs from user and db. +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_pri v | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Proces s_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ | localhost | fhgweb | x | N | N | N | N | N | N | N | N | N | N | N | N | N | N | +---++--+-+-+--- --+-+-+---+-+---+--- ---+---++-+++ and from the db table... mysql select * from db where User like 'fhgweb'; +---+---++-+-+-+ -+-+---++-++ + | Host | Db| User | Select_priv | Insert_priv | Update_priv | Delete_ priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alt er_priv | +---+---++-+-+-+ -+-+---++-++ + | localhost | fhgdb | fhgweb | Y | Y | Y | Y | N | N | N | N | N | N | +---+---++-+-+-+ -+-+---++-++ + 1 row in set (0.01 sec) Paul DuBois wrote: At 10:02 -0400 10/16/04, Michael J. Pawlowsky wrote: I just noticed that a restricted user to only one database can still run show databases; and see all the names of the databases in MySQL. You would think that it would only return the databases that that user is allowed to connect to. Is there a way I can show only those databases that he has rights to without giving him rights to the mysql database to use the db table? Make sure that the user doesn't have any global privileges that apply to databases. If the user has such a privilege, SHOW DATABASES will display all databases. (To check this, look at the privilege columns in the mysql.user table for the user's account record.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show databases shows all even if no rights;
I just noticed that a restricted user to only one database can still run show databases; and see all the names of the databases in MySQL. You would think that it would only return the databases that that user is allowed to connect to. Is there a way I can show only those databases that he has rights to without giving him rights to the mysql database to use the db table? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL valid characters for passwords
I'm not really sure what is invalid. I did not think anything was. But are you adding them as with the PASSWORD function. Take a look at: http://dev.mysql.com/doc/mysql/en/Passwords.html Mike Charlene Wroblewski wrote: I haven't been using MySQL very long, but I have managed to secure the users in my database. My problem is what are valid characters for a password. I've tried using valid unix passwords and I can't log in. I'm trying a password like 'ab#CD*12'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access Denied
Since I see this question every single day hereI will post this link. http://dev.mysql.com/doc/mysql/en/Access_denied.html It is a decent page on the MySQL site that gives you reasons why you get Access Denied. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number of Rows in DB.
Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
I'm want ALL the rows from the WHOLE DATABASE not just one table. I could do a show tables and then loop through each one and perform the select count and add it up. But I'm hoping to find a better way. As for the script, simply read the mysqldump man page. It is in the examples near the bottom. In this case I needed to tweak it a bit by adding some quotes (-Q) and extenderd inserts etc. Otherwise I was gettting errors. But I ran the stock example on the man page for a couple of years now on 2 other databases. Mike Sujay Koduri wrote: hi.. you can use... select */count(1) /*from table_name; (it will traverse only column 1) as count(*) will go through all the columns, it will take much time compared to this which generally will take less time. by the way, can you send the shell script which you are using, it might be of some use to me. Thanks sujay -Original Message- From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:24 PM To: [EMAIL PROTECTED] Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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: Number of Rows in DB.
Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); [EMAIL PROTECTED] wrote: try: SHOW TABLE STATUS Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16 AM: Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- 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: Number of Rows in DB.
Well basically what I did was simply move a script that I've been using on other databases and tried it on this new LARGE db. And luckily the number or rows were off by something like 30,000 so it was easy to figure out something went wrong (got total from phpMyAdmin) I then tweak my dumps so that it works. I'm just worried that some new data might be introduced into the db that might wreck my dumps again. So I wanted a way to compare the databases. To be honest if I'm off a couple of rows, that won't bother me too much. But if it's more than that I will need to investigate. Mike Michael, I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
On unix? netstat -l(as root -- that's lower case L) Marc wrote: == How do I check on what is binding port 3306? I don't see mysqld running and running mysql gets a Can't connect failure. --Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
actually use netstat -l -p That way you get the process id then you can ps -eaf | grep (PID) to see what is listening. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
They do not... But I think I will just re-write my script in php command line and loop through it. It was just that I loved the simplicity of the bash script. [EMAIL PROTECTED] wrote: Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost 1,000,000 rows. Not sure this is the most effecient way. But I do appreciate the effort! I think the idea of a php script that loops through show tables to get the table names and then does a Select COUNT(*) on each one and sums it up would probably be more effecient. Cheers, Mike Eamon Daly wrote: Well, here's a quick and dirty way to do it: #!/bin/sh mkdir /tmp/count chmod a+rwx /tmp/count cd /tmp/count mysqldump -T . production wc -l *.txt production rm *.sql *.txt mysqldump -T . standby wc -l *.txt standby diff production standby rm -rf /tmp/count Creates output like so: 20,21c20,21 3 users.txt 952 total --- 6 users.txt 955 total Eamon Daly - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 14, 2004 5:54 AM Subject: Number of Rows in DB. Does anyone know of a quick way to get the number of rows in a database from the command prompt. I run a cron job that does a mysql dump nightly from a prod server piped to a standy server. It's a 2 line shell script including outputting the date at time. After the dump, I would like to output the number of rows in the prod database and the newly updated standby database to make sure these match. The output is e-mailed to me, so in the morning I could simply see if they match and be assured that the dump went well. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL beginner question
Having duplicates is not a problem. As long as you don't have a UNIQUE index on it. Something else is happening in your code that is putting out the HTML I would guess. P.S. It should be SELECT * FROM jspCart_products; (your table, not your database) B Wiley Snyder wrote: Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Failed to start in Fedora Core 2
Anyone else getting a MYSQL [FAILED] when it starts up with Fedora Core 2. The funny part is that it is started and accepting connections. It's just the message comming back as failed either at boot up or when doing a /etc/init.d/mysqld start Regards, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting dumped data back into new db
Generally it's just sql. The easiest way would be from a command line on the server. mysql -p dbname mydb.sql Cheers, Mike [EMAIL PROTECTED] wrote: Hi, I dumped an entire db (using phpMyAdmin, MySQL 4.0.18) I have a file now: mydb.sql Again, this is the entire db. Now I have recreated the db and all the tables, rows, etc. All I need to do is to get the data (data only, db is built) that goes into those tables and rows from the file into the new db. How? Thank you very much. Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting dumped data back into new db
Don't do it from within mysql. Do it from your shell prompt. [EMAIL PROTECTED] directoy]$mysql -p database database.sql -p is used for prompting for a password. You only need it if you need a password. means to redirect the sql file as input to the mysql command. database is the actual name of the database. Mike [EMAIL PROTECTED] wrote: I get: mysql -p database /Users/me/Desktop/database.sql; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-p database /Users/me/Desktop/database.sql' at line 1 Thanks, TR On Aug 22, 2004, at 6:09 PM, Michael J. Pawlowsky wrote: Generally it's just sql. The easiest way would be from a command line on the server. mysql -p dbname mydb.sql Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting dumped data back into new db
Just thought I would add you should take a look at the man page for mysqldump. At a shell prompt type man mysqldump. Michael J. Pawlowsky wrote: Don't do it from within mysql. Do it from your shell prompt. [EMAIL PROTECTED] directoy]$mysql -p database database.sql -p is used for prompting for a password. You only need it if you need a password. means to redirect the sql file as input to the mysql command. database is the actual name of the database. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: special order
select id from mydata order by name. If that puts the #125 first etc. simply created a field called 'order_field' or whatever and put numeric values in them. The SELECT id, name FROM mytable ORDER by order_field. dan orlic wrote: Ok, I have a client that has a specific request. the ordering of data. For example current the order by returns this: id | Name -- 1 | #124 2 | #125 3 | #155 4 | apples 5 | bacon but he would rather see the data as : id | Name -- 1 | apples 2 | bacon 3 | #124 4 | #125 5 | #155 how would I contruct that query? many thanks... dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Question
Im not sure if this is possible or not. I have a Sales leads table. Part of the table has 2 employee_ids. 1. The Sales person the lead is assigned to. 2. The Marketing person that generated the lead. Then there is a employee table that has ids and names. When generating a report for leads I would like to lookup the name of the employee. I know I can do it with a seperate query, but I'm wondering if I can also do it in one query. Something like: SELECT employee.name as sales_name, employee.name as marketing_name, leads.id FROM leads, employee WHERE employee.id = leads.salesid AND employee.id = leads.marketingid Is there someway this can be done? Thanks for any assistance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
Thanks a lot Michael. A regular join did not seem to work. But when I tried a LEFT JOIN it worked. A cut down example of it is the following. SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as marketing_name FROM global_lead LEFT JOIN global_employee es ON global_lead.rep_no = es.id LEFT JOIN global_employee em ON global_lead.entered_by = em.id WHERE global_lead.rep_no = 8 Michael Stassen wrote: You need to join the employee table twice, once for each id lookup, like this: SELECT es.name AS sales_name, em.name AS marketing_name, leads.id FROM leads JOIN employee es ON leads.salesid = es.id JOIN employee em ON leads.marketingid = em.id; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Title Case Problem
Use INITCAP. SELECT name, INITCAP(name) new_name FROM customer WHERE firstname IS NULL *** REPLY SEPARATOR *** On 5/5/2004 at 12:43 PM Brian Mansell wrote: I may be wrong, but there isn't an easy method for completing this in SQL alone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get around lack of views?
How would I do this? Let say I have an employee table with Name varchar(64) Dept int(11); Salary int(11); I want to grant select on Salary to a mysql user but only where dept = 1 let's say. Normally I would create a view to do something like this. But I was wondering if there would be another way around this until views are implemented that someone has thought of. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
