MYSQL :: Testing For Bit Set In Int Field

Testing MySQL
I'm trying to test MySQL (i've already tested php and it works, so I'm not real sure I need this step, but I'm sure I need to know how to do it reagardless).

I test it using localhost as the hostname and it works except that this is what the page looks like. ....

DB Testing Software
Are there any available tools for testing mysql db performance?
What do you guys typically use for testing your db's performance and data integrity?

MySQL Testing
I just recently installed MySQL and am also running an APACHE 2.0 server on my computer (Windows XP machine). I followed the instructions in the help document on installing MySQL and I believe it is installed properly, no errors occured, it was pretty straight forward.
However, when it comes to testing, the documentations on give commands using the "Windows Command Line Client" and not the MySQL Command Line Client.
I installed MySQL as a service, that turns on and off with Windows.

How Do I Set Up My System As A Testing Server
I work on a Windows XP Pro. Platform and I am having trouble setting my system up as a Testing Server for PHP pages. I want to be able to test scripts and build my database. I tried downloading and installing MYSQL and PHP but it still does not work. Is there some kind of Configuration I need to do in the PHP.ini file?

Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?

Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?

Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to
stress test. Can someone please point me to some testing tools we can use?

Testing SSL Conections W/ MYSQL
I recently installed all MySQL 4.1.1 including max RPMS. During the
installation i used "--nodeps" on MAX package and at Shared-compat
cause they were requiring the libssl.0.9.6 and libcrypto.0.9.6. I am
using a RH 9 and these files are installed on my system.

The result for mysql query :

show variables like 'have_openssl';

is

have_openssl | YES

I created a user using the grant command with 'require ssl' like
this:
[color=blue]
> grant insert,select on MyDatabase.* to myssluser@myhost require ssl;[/color]
Query OK

I can connect using this username and CLIENT_SSL param (isn't this
param just for php 4.3 or higher?):

mysql_connect($dbserver, "myssluser", $dbpass, CLIENT_SSL);

It runs on my php 4.2.2 compiled with openssl 0.9.7, but i need to

TEST if this is a real ssl connection.

Testing If Mysql Is Running
how can i test if mysql is running on my machine?

Load Testing MySQL
I know this is quite a bit off topic, but i was wondering if anyone else
out there had any experience with Segue's Silk Performer load testing
tools. More specifically, i am trying to compile a test script to allow
me to perform some transactional database testing to load test a MySQL
4.0.13 database server running behind some apache webservers. I have
obtained an ODBC driver from MySQL and have some specific queries i
would like to use in the test. I am have used SP to load test the
webservers specifically, but i am not 100% sure how to run this against
a database server now.

If anyone has any experience they can share, please feel free to email
me off list.

Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE.
However, when I tried testing that this works by entering:-
select * from address, name where address.name_ID=name.ID;
It says empty set. What am I doing wrong?

Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys.

The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again.

for example:
select * from table where id=2 order by index_field desc; 4/5 seconds.
select * from table where id=2 order by index_field desc; 0.02 seconds.

select * from table where id=3 order by index_field desc; 4/5 seconds.
select * from table where id=3 order by index_field desc; 0.02 seconds.

etc, etc...

Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE.

However, when I tried testing that this works by entering:-

select * from address, name where address.name_ID=name.ID;
It says empty set. What am I doing wrong?

Testing Parameters Within A SELECT Statement
I am building a SELECT statement where I will be passed search parameters depending on what user has entered. Each parameter may have a value or maybe NULL.

What construct (CASE, IF, etc) can I use to build my WHERE clause so that the passed value is included or if NULL, all rows are returned.

SELECT *
FROM table
WHERE col_a = val_a (or if VAL is NULL then all rows)
AND col_b = val_b (or if VAL is NULL then all rows)
AND col_c = val_c (or if VAL is NULL then all rows)
etc ...

Bogus Datasets For Database Testing
I'm teaching myself mysql and wonder if there are any sources of
inane, complex, bogus data that I can download and insert into a
table(s.) I have created some very small sets to practice joins and
whatnot but they are not very big or complex, or exciting.

compressed tar of text strings (no blobs) could have plenty of data
but still be a manageable download. It would also be a nice test of my
machine and tuning skills.

ERROR 1045 When Testing In The Control Center
In the MySQL Control Center 0.9.4-beta, I right click the top level server name and choose the Edit option. Then I have entered a User Name and a Password in the General TAB. I then mark the option "Promt for Password" and hit the "Test" button.

I get a small window where I should enter the password for User name@Host name. I enter the password and hit the OK button. Now I get the error message "ERROR 1045; Access denied for user; 'User name@Host name' (Using password: YES)"

When looking in the Help menu, the Contents - F1 is not activated, so I can't get any online help.

I'm just wondering why the test for the password failed and why there are no online help in the consol?

Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine.

The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok?

Is there anything else I should be doing, or is what I am doing correct?

Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this?

Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh.

What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.

Testing Access To MySQL-DB In Windows-Network
Background: I am writing in C# a program, which reads data out of a MySQL-DB. When the DB is on my local developer PC everything is fine. But when trying to access the MySQL-DB on a server in our Windowsnetwork, I receive the message: "Host '**' is not allowed to connect to this MySQL server".

Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like:

SELECT
yada yada yada
FROM
schedule AS 's1'
WHERE
(
SELECT
skippedstop_id
FROM
skippedstops
WHERE
schedule_id=s1.schedule_id
AND
location_id=$origin
) == "EMPTY SET"

Only I have no idea how to actually test for the empty set from the nested select...

Transferring Tables From Testing Server To Online Server
I need to copy my tables from a testing server on a local area network to my hosting companies online server. Is this something I can use MySQL Query Browser to do, and if so how?

Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id.

How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously?

My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense).

Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.

How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06)..

There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?

Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields,
I want to put each field into a Bash variable so that I can handle each
field as an entity.

The query I have is something like this:
mysql -e "use $database; select field1, field2, field3..., from Table1 where
fieldN like '%something%';"

can I / How do I do something like:
mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;"

do
stuff with var1, var2...,
done

One Field With Multiple Attached Rows, Only Show The Field Once?
I have a table called "Customers", with a field called "Customer name" and a "Customer ID". I have another table called "Projects", with a field called "Project name" and a field called "Customer ID" which references to "Customers"

What i am trying to do is: Select a customer. Select the projects that are related to the customer by comparing the Customer ID field. Code:

Create A Field By Deleting A Part Of The String From Another Field!
I have a table for "Addresses", which has a column titled "Address" and the records in this column are a combination of house#, street name and street suffix, which typically looks like "100 Main Street".

I am looking for a query result wherein a new column would be created which would have only the street name and the suffix and would eliminate the house #s, hence the result would look like "Main Street".

Copy Field Over, Then Update The Original Field
I want to change a price of a product, but back up the old price. I was trying to do this through two queries, an INSERT and an UPDATE, but it's not working.

INSERT the row that will store the backup:

INSERT
INTO
s01_MUS_SalesXProducts
(
s01_MUS_SalesXProducts.sale_id
, s01_MUS_SalesXProducts.prod_id
) VALUES (
".(int)$sale_id."
, ".(int)$prod_id."
)
Then I would copy the prices over and update the original with one UPDATE query:

UPDATE
s01_Products
LEFT
JOIN
s01_MUS_SalesXProducts
ON
s01_Products.id = s01_MUS_SalesXProducts.prod_id
SET
s01_MUS_SalesXProducts.old_price = s01_Products.price
, s01_Products.price = ".(float)$sale_price."
WHERE
s01_Products.id = ".(int)$prod_id."
However it is copying the new price to the backup field. Is there a way to do this in two queries? I know if I select the product price before inserting I can do it in 3. Truely I would like to do this in 1, but I don't believe this is possible due to the nature of the queries.

Creating Field In Table To Sum Field Values
i was wondering if there was a way in mysql to have one record that will sum up all each field value(one record with each field having sum totals)? i have a table with over 60 statistical fields that i want to get the sum for.

Updating Field With Contents Of Temporary Field
I have a table, that when new information is entered for it the new info is held in a temporary table that has exactly the same fields (the only difference is the auto increment pid in the original table is not auto increment in the temporary one - it holds the pid from the original).

I need to be able to update the original with the contents of the temporary field, all fields are updated.

Is there an easy way to do this, or will I need to select from the temporary and then run the update setting all the fields explicitly.

I have tried something like [code]
Update villas SELECT * FROM updatevilla WHERE pid='$villapid'
[/code ] in php but it doesn't work. I think I see what some of the problem is but am not sure how to fully write the query.

Orderby A Text Field As It Were An Integer Field
Is it possible to construct a query so that the orderby 'field' returns the rows as though the 'field' were an integer field, even though it is a text field? I do not want e.g. 10 to be before 2.

The field in question is called 'lagerantal' and is of type 'text'

Import NULL Or Empty Field Field Value
If you are importing data from an external data source and you have the choice of populating empty fields with NULL, what are the pros and cons of doing so?

Copy Partial Field Value To New Field
I have a table with state and county in one field. The first 2 characters in all the records indicates the state. Is there a way to copy just these first two characters into a new field in mysql?

Given Field Name, Find Table That Has Tha Field Name?
Is there a quick way to identify tables that have a specific column in them? For example, if I have a column name 'RuleName', how can I need to find all the tables in a database that have a column with that name?

Date Field, Time Field
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) and to be able to sort the query results on the resulting field.

Using A Field Alias In Another Field
I'm migrating a DB from Access to MySQL and I having problems with queries similar to this:

SELECT 1 AS x, x+1 as y;

I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error.

Is there a way to do this without calculating 'x' twice?

I Want Field Data Without Field Name, How?
I am retrieving data from mysql under linux. something like mysql -e
"select id from table1", I get output

id
1
2

but I dont want the field name "id" but only the data. How can I do it?

Field Contains Form Field
I have a form putting variable search_var

Select * from training.itdtms_content where content_Body like #search_var#

the # on each side are for the Coldfusion variable.
I want to be able to find records that have a search term inside them, can anyone help?

Part Of A Field Into Another Field
I have a table with an email field that contains values of the form
uid@domain. I need to populate a new field called uid with just the uid
part of the email address. Is there any way of using select to just
retrieve the part before the @ and insert it into the uid field?

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

Update One Table Field With Another Table Field
i am having a problem in my update function. I have two tables:

buyselldata table and autozack_signal table

buyselldata bsignal coloum has to be updated with Close_Price in autozack_signal table if macd_buy in autozack_signal is 1

common field in both tables are Script_Name

"Update buyselldata b
set b.buy = a.Close_Price
where exists(select Close_Price from autozack_signal a
where a.script_Name = b.script_Name
and a.macd_buy=1)";

i am getting -1 for mysql_error()

Updating Table Field From Another Table Field
I have a field called 'tel1' in table 'live_properties'
and I want to update a 'tel1' field in 'live_properties'

with

'phone_num' field from 'usersTBL'

MYSQL code is here but giving error any suggestions

UPDATE live_properties SET tel1 (SELECT phone_no FROM usersTBL WHERE username = live_properties.username)

Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.

Field Value = Foo AND != Bar
part of my query is "WHERE snafu = foo AND snafu != bar"

Can that be simplified? Like "WHERE snafu = foo AND != bar"?

Last Seen Field
On my 'users' field, i have the required fields for a user member registration.

CREATE TABLE `users` (
`Id` int(6) unsigned NOT NULL auto_increment,
`username` varchar(30) default NULL,
`pass` varchar(30) default NULL,
`fullname` varchar(100) default NULL,
`email` varchar(100) default NULL,
`created` datetime default NULL,
`acces` int(11) default '2',
`lastseen` datetime default NULL,
PRIMARY KEY (`Id`)
)

The registration datetime to be posted/called on various pages, admin stats, i am calling with: NOW()

Question: How can i solve the 'lastseen' field. What function to use? Or how to call it, i wan't to see on the admin users statistics pages, when a user member has accesed the website.

Can I Use ALL As A Field Name?
I have a table that has the 3 letter currency codes of the world currencies
as the field names. The Albanian Lek currency is know as ALL. When I goto
save to this field I get an error presumably because it is a word used in
MySQL. Is there anyway to store/read to/from this field keeping ALL as the
field name - without generating an error?

"INSERT INTO a_currency_exchange_gbp SET
AED='$aed_gbp',
AFA='$afa_gbp',
ALL='$all_gbp',
AMD='$amd_gbp',
ANG='$ang_gbp',
AOA='$aoa_gbp',
etc

INT Field Won't Go Beyond 127
Points INT(3) is a row in a table. I use PHPMyAdmin.

Each week, I have to update the numbers in the row, and this week some of the rows should read 146, or 140, or 136.

However, I enter the number - say 146 - but when it is saved, it turns the nuumber to 127. And numbers I have entered of more than 127 display only 127!

Help! In desperation, I changed the INT to INT(4) and then INT(5) and changed the name of the row. None of these affect the result, which remains at 127.

Is this a clear indication of insanity, or something a little less disturbing, please, Experts?

Add 1 To A Field
I have a int field. Each entry into the database I want to add 1 to this (i.e. 70,71,72). I've tried $therow = $row['entryno']+1, but that doesnt seem to work...

Get Field Value
I am using PHP with MySql. I have a statement:

$sql = "select * from Validnames where UserID = '$UID' && Location = '$Location'";

Am I correct this gets a single matching row of data from the database. If so, how do I pull out the value of 1 of the fields in the row. (2nd column)

Field Contains
How would I do a Select statement looking for a specific word or group of words in a long text field?

Select *
From centers
Where c_specs "Contains" 'The Words I'm Looking for'

How do I run that SQL command on a long text?