MYSQL :: Innodb Multiple Tablespace & Unique Name

InnoDB Binary Logs - Unique To Each Database Configuration
I would like to back-up our InnoDB databases using a combination of mysqldump and the binary logs. We have multiple databases within our server instance, and each database is unique to a customer so we don't want to mix their data. I know how to create / schedule unique dump files for each database, but was wondering how to configure unique binary logs. Right now all transactions are logged in the same set of files.

How Do I InnoDB Multiple Tablespaces, On Multiple Disks?
I want to use multiple tablespaces with InnoDB tables.

And I also want to spread my InnoDB tables on two separate disks.

I also want to control which disk each of the InnoDB tables are placed.

Is this possible?

Multiple Unique Indices
Consider this table:

ID | Number | Name
1 | 10 | X
2 | 11 | Y
3 | 12 | ABC
4 | 13 | X

ID is the primary key, and there is a unique index on Number.

My program loads the table for editing (ID is read-only, Number and Name are modifiable), then checks through each line if any was updated by the user and writes the record back to the database using UPDATE statements (the ID must stay the same).

Consider that the numbers of Y and ABC are exchanged, such that:

ID | Number | Name
1 | 10 | X
2 | 12 | Y
3 | 11 | ABC
4 | 13 | X

When my program executes the query "UPDATE Table SET Number = 12 WHERE ID = 2", the unique index throws an error since ABC (ID=3) still has Number=12.

How could I avoid that and update both rows safely (without first deleting all records)?

Multiple Non-Unique Columns
I'm trying to store the call detail reports from a phone system to a database. I've successfully been able to read the CSV files it gives me into an InnoDB table using PHP. I just realized that the system does not give a single unique key in the raw data, but that two columns together will identify a unique call entry.

The columns if interest are the timestamp (two people could conceivably make a call at the same time) and a call identifier (which seems to be unique over a month or so, then the system starts recycling the call identifier). With these two columns you can identify a unique call over ANY given time. I'm not sure how to use that fact to assure I'm not inserting the same data if I were to read the same CSV file twice.

UNIQUE As Multiple Columns
its possible to define multiple columns as being UNIQUE - not the value of either column, but the two together. For example, you're storing people's names and addresses in a table. Two or more people could have the same lastname, and more than one person will surely have the same firstname. However, no two people can share the same lastname AND firstname. Is there a way to set this constraint?

Turning Off Allowing Multiple Non-unique Rows
I feel lazy and I am busy so i don't want to fix my perl code to only alllow insertion of unique rows. I know mysql is set to allow multiple rows of the same thing but I would like to turn that feature off. Is that possible?

InnoDB & Multiple Mysqld's On 1 Server
i've been weighing the pros and cons of running multiple concurrent
mysqld's on one server, to have better control over what databases are on
what physical disks.

System: 4 processor sun box running solaris with eighteen 36Gb drives.

The situation is that i have a bunch of databases on one server that can all
be classified as either external use or internal use. The internal use
databases are consistently hit pretty hard, and we want this to have minimal
impact on the external use databases. Currently we're using 64 index MyISAM
tables, and with carefully choosing mount points for various physical
devices, we have the databases separated as we want them.

It is my understanding that with InnoDB, all tables are put into the
configured InnoDB file(s) together, which would violate what i am trying to
accomplish.

The only solution i have come up with to control the physical location of
InnoDB databases is to run multiple mysqld servers, each one with its InnoDB
files on the desired device.

Has anyone experienced any success or failure with this sort of configuration?

Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).

Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email)

Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact.

I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column.

I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates).

I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address
AND localhost@my_field_db FROM my_field_tablet_pc to affect
a custom sycnchronization that SQLyog will not allow.

Both remotehost and localhost are built on WAMP stacks.

Extract Multiple Columns From Multiple Tables
For hours and hours I've been trying to work out how to write the sql query to extract some data from some tables, but with no luck.....

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

Multiple Languages, Multiple Products
I posted before and was helped immensely. I am designing a multilingual-capable site where each product may (or may not!) have a description in the user's chosen language. In this case, I want to get the default language description.

Say my product descriptions table has fields prod_id, language, and description.

When I have a single product and I want its description, I can retrieve it as follows:

Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH
LINUX)and I'm wondering which is the best option to do :

I currently have one file for each of my modules, (I'm using filepro plus) ,
so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with
their own fields.

1) Should I create multiple databases on MySQL

2) Should I create a single database on MySql and with multiple tables ?

Which one of these options is the best and more safer ?

Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time.

I have to decide if I should use :

1)one database with one table for all users.
2)one database for each user.
3)one database with one table for each user.

I would really appreciate if you can tell me what choice is best and why.

Unique ID
Say I wanted a membership site. A user would register and get a auto created ID.. Starting from 1..

I want people to buy a User ID if they want..
Say I get the ID 567 but want to purchase the ID 5555 because I like the number. But its not created yet since there arent that many users.

Is this possible with an auto icrement ID setting for the users without messing up the flow of the auto increment field if so how would I go about it.

Unique
How can i define in mysql table that a field is unique??

Set A Unique Row
I have a website which is database driven (it's a world cup prediction league).

The problem i have is that if a user changes their predictions, the script reposts every prediction again in the database.

The database table name is:
plpredictiondata

This consists of 6 rows. The ones i am interested in are called:
userid
matchid
predtime

What i need to do, is every time that predictions are posted, the row matchid should only contain a unique number per userid. Does that make sense??

As an example, say i have 2 users (userid=1, userid=2), and 2 matches (matchid=1, matchid=2). Userid=1 posts scores for both matches, then subsequently changes them. At the moment, it will show userid=1 as having matchid=1 & 2 in the table twice. I want it to only allow the most recent prediction. I have tried using REPLACE INTO, but thats not working.

UNIQUE
I am trying to create a statement where it will return the UNIQUE values
from a table, I think its the UNIQUE statment.

So a table may have 60 entries, 20 of them are X, 20 Y and 20 Z
I want it to return only X,Y,Z e.g. 3 returned entries.

Unique Value
I have a table like this:

CREATE TABLE IF NOT EXISTS `test` (
`id` INT(11) unsigned NOT NULL auto_increment,
`cid` VARCHAR(10) NOT NULL,
`item` VARCHAR(10) NOT NULL,
`val` VARCHAR(10) NOT NULL,
`dt` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

How do I find the unique highest value in `val` ?

Unique Row Problem
I have this statement that shows who made affiliate commisions during the month I choose.

$sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN (";
$sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'";
$sql .= ") GROUP BY id";
I run it and it runs correctly except for the fact that if an affiliate makes a couple sales from the same person, its not showing. Its only showing one sale.
Is this because this statment is only pulling out unique id's? If so, how can i fix it?

Best Way To Do This... Unique Key But 3 Rows Need It?
I have a weekly schedule table, each week should have a unique number but each week requires 3 different records (one for each game). What would be the best to create a unique identifier that is the same for the 3 records each week?

Unique Results
I am querying my mysql database from php like so:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts
LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
WHERE $wpdb->post2cat.category_id NOT IN (2, 6)
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";

Its joining a second table (post2cat) as the main table (posts) does not have a value (category_id) which I need to check is not 2 or 6. As a result if an item in posts has two category ids (and so is listed twice) the item goes into my mysql result twice which I don't need. Is there a way I can return only unique results (i.e. not twice)??

This is the Wordpress mysql structure if anyone is familiar with it.

Unique Key Pair
I'm very new to MySQL, and therefore don't even know what to search for when it comes to this issue (I've tried about a dozen terms to no avail).

My table is set up like this:

CREATE TABLE `tableName` (
`sessionID` int(10) unsigned NOT NULL default 0,
`name` varchar(40) default NULL,
`value` longtext NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1
`sessionID` actually corresponds to another value in a different table. From what I understand, I've set up a "one-to-many relationship" (`sessionID` must be unique in the other table, but not this one). However, I would like to add some kind of a constraint that requires that `name` is unique within the context of it's `sessionID` value.

For example, this would be allowed:

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
But this would not, because there would be two identical `name` values ('thing2') for one `sessionID` value (2):

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
2'thing2''error'
Is this possible, and if so, how can I state this in my CREATE TABLE query? Also, does this have a specific name (so I can look up more information if need be)?

Primary Key + Unique Key?
my question is: do I have to declare primary key and unique key in this table?

CREATE TABLE `sessions` (
`id` varchar(32) NOT NULL default '',
`access` int(10) unsigned default NULL,
`data` text,
`usr` varchar(12) default NULL,
UNIQUE KEY `id` (`id`)

Unique Query Help
I have two tables

Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)...The only way to display the combination for a particular URL is to use group by.

Table 1

id url
1 hotmail.com
2 sitepoint.com
3 youtube.com

Table 2
id userid (user who added) url notes
1 user1 hotmail.com good site
2. user2 hotmail.com email site
3. user3 hotmail.com great site
4. user4 hotmail.com cool site

Now I want to JOIN table 1 and table 2 and display unique records for hotmail.com...Only way i can do is by using group by...I don't want to use that. And it doesn't really matter if it display record by any user, but as long as it is only one record for hotmail.com in Table 2

Can anyone please help me

If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.

A 'UNIQUE' Question
I have to check whether my db contains same value.
so i have altered my table as

ALTER TABLE details ADD UNIQUE (name);

so if i am giving same user name mysql will throw a false error

is it a good method ? or

should i use select query to check whether name exist or not

Unique Id Between 2 Tables
Is there a way to give a feild, lets call it "id" a unique value but without using autoincrement?

Why? I need to have 2 seperate tables ("bands" and "soloists") which both have the field "id" but there cant be a collision in the id's.

Index & Unique Key
I created a table with two columns 'user_id' and 'name', and defined the combination of the two columns as UNIQUE and defined an INDEX on each column.
Why is the following error generated: UNIQUE and INDEX keys should not both be set for column `user_id`?

Unique Identifier
I wanted to create a unique identifier field in a mysql database, this is a fairly common practice in MS SQL, is there an equivalent in mysql?

How To Make A Unique Key 1 Again?
I want to make a unique number 1 again.

Creating A Unique Value
I'm trying to create a stored procedure which creates a unique value each time.

This one creates the a value based on what length I want to give it and prints it.
CREATE PROCEDURE `test3`(IN idlength int)
BEGIN
declare uid char(30);
declare a char(1);
set uid='';
while length(uid) < idlength do
set a= substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',floor(rand()*64), 1);
set uid = concat(uid, a);
end while;
insert into t2 values (uid);
select UID;
END $$

What I need to do is to compare this value with the already stored values in the table to check if the new one is unique. Any suggestions on how to do that?

Getting All Unique Entries
I need a query to get all unique entries in a field.

If you have:
3
3
5
4
5
3
5
5

it would return 3, 4, and 5.
Can this be done in a MySQL query.

Unique In Mysql 3.23.52
i have to use mysql 3.23.52. I have problems to create UNIQUE over two columns. Is it possible to create uniqueness over two columns? It is also not possible to create an Primary Key over two columns.

Counting Unique
I have the following code...

$query = "SELECT DISTINCT newsid FROM comments";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){

which works fine. My questions is there any way to determine which newsid shows the most amount of times. I'm trying to make a "top5" sort of things and it would make it much easier.

UNIQUE Constraint
If I have a table with a UNIQUE key 'v', trying to store upper and lower case letters should work, but won't. For example, if I insert a value of 'a' for the column v, and then try to insert a value 'A', I get an error message. Likewise, if I enter this statement:

select * from tname where v='A';

the display will include rows where v='a'.

Surely there must be a way to force MySQL to recognize the difference between upper and lower case values, and to specify that they be stored that way?

Get The Next, Unique Primary ID
I have a table with an ID (Primary, Auto-Increment), and some other fields. The problem is, whenever I insert a new row and leave the ID field unspecified, it inserts the new row with an incremented ID, EVEN if my records look like this:

ID:
1
2
7
8
9
10

Notice how above, the records between 2 & 7 appear to have been deleted? I want mySQL to recognize this when inserting new records, and to use a number that would be the next available, unique number.... So instead of inserting the new row with an ID of 11, it would insert the new row with an ID of 3 - filling up the unused number.

Primary Key & Unique Key
When running PhpMyAdmin versin 2.8.x, I notice a warning message on tables that have PRIMARY & UNIQUE Key on the same column (field).

It says that Primary & Unique Key SHOULD not be set both on the same column.

Is there any body can advise me how to appropriately handle Primary & unique key on the same field ?

Unique Row Not Column?
I know Mysql is able to prevent duplicates being entered in the one column, via the 'unique' feature.

Is it possible to stop rows of the same values being created.

For example
joining two or more columns in a row, and merging the values into a singular value, upon which the 'unique' feature may work. Or is their more of a simple way of doing this.???

Invoice_ID Products UNIQUE
56 ---------- X --------- 56X
56 ---------- Y --------- 56Y
56 ---------- X --------- 56X
57 ---------- Y --------- 57Y

Unique Column
Each time I try to insert multiple rows of data, MYSQL will stop when it finds the first duplicate. I'd like to know how I can have it continue on and either suppress the error message completely, or put the duplicated entries into an array and output them afterwards.

Unique Constraint
tell me is it possible to set a unique key constraint over a Blob field?
If it is possible could you please tell me how can i achieve this.

My Scenario is i got a sub_photo table(sub_phid intger primary key,sub_id,sub_photo)
i want (sub_id,sub_photo) as unique. Please tell me how can i go about with this.

Unique Key Also Act As Index
someone said that a unique index also acts as a normal index for the respective fields. Is that true?

I :
- need unique constraint and indexing on a field
- don't need redundant indexing on the field

So I don't need to add a normal index on the which has unique index already applied, is this correct?

Unique ID Field
Hi, i need to create a table which will create a unique field called tracking_number. But i want this tracking number to always be 10 characters long and start at say 1230000000 instead of 1 because it would look a bit stupid with the first order on the system by the customer being 1!

Would really help me if any one knew the code to do this.

This is what i have but tracking_number is stating at 1.

<?php

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE jobs3(
tracking_number INT ( 10 ) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(tracking_number),
delivery_address VARCHAR(100),
date_dispatched VARCHAR(100),
status VARCHAR(100),
signed_by VARCHAR(100)
)")
or die(mysql_error());

echo "Table Created!";
?>

Two UNIQUE Fields Together
I'm trying to add records to a table via command line. I would like to setup the table so that it rejects a record insert if two of the fields are not UNIQUE together.

Here is my table:

logNum mediumint(8)
logDateTime datetime
docNum tinyint(3)
docName varchar(200)
owner varchar(15)
sourceIP varchar(15)
printerName varchar(25)
printerPort varchar(15)
sizeInBytes varchar(15)
pagesPrinted smallint(10)

So, if two records have the same logNum AND the same LogDateTime. I want that record rejected and the insert to continue with the rest of the records.

Inserting Unique Value
I need to insert a unique value into a (not auto-increment) column.
I try

insert into idtest (val) values ((select max(val) from idtest)+1);

but I get

ERROR 1093 (HY000): You can't specify target table 'idtest' for update
in FROM clause

- what is the correct way to do this?