Re: error creating table

2009-12-01 Thread Sharique uddin Ahmed Farooqui
Hi Jim,

Only difference is auto increment in the field.
You cannot have two auto increment in a single table also auto
increment field must be the key.

On 11/30/09, Jim Lyons  wrote:
> I created dummy tables for Roles and Users specifying the primary keys as
> 'serial' and then tried the below syntax.  It failed.
>
> Then I redefined the primary keys in the parent tables to be the exact same
> type as the foreign keys in UserRole and it worked.
>
> So, check the datatype of all your keys and make sure they match.
>
> On Mon, Nov 30, 2009 at 11:50 AM, Sharique uddin Ahmed Farooqui <
> saf...@gmail.com> wrote:
>
>> Hi,
>> I'm developing a CMS, in which I have 3 tables user, roles and userRoles.
>> Here is the code for userRoles table. on this I'm getting error
>> creating table (error code 1005), both userid and roleid are pkey
>> (int, auto increment)
>>
>> CREATE  TABLE IF NOT EXISTS `mydb`.`UserRole` (
>>  `roleid` INT(10) UNSIGNED NOT NULL ,
>>  `userid` INT(10) UNSIGNED NOT NULL ,
>>  PRIMARY KEY (`roleid`, `userid`) ,
>>  INDEX `fk_userid` (`userid` ASC) ,
>>  INDEX `fk_roleid` (`roleid` ASC) ,
>>  CONSTRAINT `fk_userid`
>>FOREIGN KEY (`userid` )
>>REFERENCES `mydb`.`Users` (`userid` )
>>ON DELETE NO ACTION
>>ON UPDATE NO ACTION,
>>  CONSTRAINT `fk_roleid`
>>FOREIGN KEY (`roleid` )
>>REFERENCES `mydb`.`Roles` (`roleid` )
>>ON DELETE NO ACTION
>>ON UPDATE NO ACTION)
>> ENGINE = InnoDB
>> DEFAULT CHARACTER SET = utf8
>> COLLATE = utf8_general_ci;
>> --
>> Sharique uddin Ahmed Farooqui
>> (C++/C# Developer, IT Consultant)
>> http://safknw.blogspot.com/
>> "Peace" is the Ultimate thing we want.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com
>>
>>
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>


-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



error creating table

2009-11-30 Thread Sharique uddin Ahmed Farooqui
Hi,
I'm developing a CMS, in which I have 3 tables user, roles and userRoles.
Here is the code for userRoles table. on this I'm getting error
creating table (error code 1005), both userid and roleid are pkey
(int, auto increment)

CREATE  TABLE IF NOT EXISTS `mydb`.`UserRole` (
  `roleid` INT(10) UNSIGNED NOT NULL ,
  `userid` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`roleid`, `userid`) ,
  INDEX `fk_userid` (`userid` ASC) ,
  INDEX `fk_roleid` (`roleid` ASC) ,
  CONSTRAINT `fk_userid`
FOREIGN KEY (`userid` )
REFERENCES `mydb`.`Users` (`userid` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
  CONSTRAINT `fk_roleid`
FOREIGN KEY (`roleid` )
REFERENCES `mydb`.`Roles` (`roleid` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



Re: error creating table

2009-10-05 Thread Sharique uddin Ahmed Farooqui
Thanks Liu, This type mismatch issue.


On 10/5/09, LIU YAN  wrote:
>
> hi,
>
>
>
> I run your code , but worked propertly. I suggested to check the table USERS
> , ROLES, is the column userid / roleid same data type (INT(10) UNSIGNED )
> with your userroles table ?
>
>
>
> ==
>
> mysql> create table users (userid INT(10) UNSIGNED primary key);
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> create table roles (roleid INT(10) UNSIGNED primary key);
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> CREATE TABLE `userroles` (
> -> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> -> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
> -> PRIMARY KEY (`roleid`, `userid`),
> -> INDEX `FK1_user` (`userid`),
> -> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
> -> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
> -> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
> -> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
> -> )
> -> COLLATE=utf8_general_ci
> -> ENGINE=InnoDB
> -> ROW_FORMAT=COMPACT
> -> AVG_ROW_LENGTH=0;
> Query OK, 0 rows affected (0.08 sec)
>
> mysql>
>
> ==
>
>
>
> best regards
>
> liuyann
>
>
>
>> Date: Sun, 4 Oct 2009 23:47:54 +0530
>> Subject: error creating table
>> From: saf...@gmail.com
>> To: mysql@lists.mysql.com
>>
>> Hi,
>> I'm trying to create a table with 2 columns both are primary key
>> (combined) and both are foreign key as well. I'm getting error cannot
>> create table. Here is the sql
>>
>> CREATE TABLE `userroles` (
>> `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
>> `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
>> PRIMARY KEY (`roleid`, `userid`),
>> INDEX `FK1_user` (`userid`),
>> CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
>> (`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
>> CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
>> (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
>> )
>> COLLATE=utf8_general_ci
>> ENGINE=InnoDB
>> ROW_FORMAT=COMPACT
>> AVG_ROW_LENGTH=0
>>
>> --
>> Sharique uddin Ahmed Farooqui
>> (C++/C# Developer, IT Consultant)
>> http://safknw.blogspot.com/
>> "Peace" is the Ultimate thing we want.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com
>>
>   
> _
> Windows Live: Keep your friends up to date with what you do online.
> http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_1:092010


-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



error creating table

2009-10-04 Thread Sharique uddin Ahmed Farooqui
Hi,
I'm trying to create a table with 2 columns both are primary key
(combined) and both are foreign key as well. I'm getting error cannot
create table. Here is the sql

CREATE TABLE `userroles` (
`roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`roleid`, `userid`),
INDEX `FK1_user` (`userid`),
CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users`
(`userid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles`
(`roleid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE=utf8_general_ci
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AVG_ROW_LENGTH=0

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



linq with mysql

2008-11-25 Thread Sharique uddin Ahmed Farooqui
Hi,

 I'm using VS Express 2008, and trying to use linq with Mysql.What I have done

   1. Created a db with a single table name account(fields : acct_num
int, amount int)
   2. created a Linqto sql file with same model as db table.
   3. A page with gridview and code behind is (on page load event):



MySqlConnection con = new
MySqlConnection(ConfigurationManager.ConnectionStrings["mysqltest"].ConnectionString);

MyLinqDataContext db = new MyLinqDataContext(con);

var q = from p in db.Accounts  p;

GridView1.DataSource = q;
GridView1.DataBind();

 I'm getting this error:

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 '[t0].[amount], [t0].[acct_num]
FROM [Account] AS [t0]' at line 1

I run  this query  in a mysql tool (HeidiSql), it gives same error. Is
it due to brackets '['  ?

 select [t0].[amount], [t0].[acct_num] FROM [Account] AS [t0]

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



roles table design

2008-11-24 Thread Sharique uddin Ahmed Farooqui
HI,
I'm developing a cms, I need some suggessions regarding database design.
I'm creating role table in which role name will be unique, so my
question is that should I create roleid(int, autoincreament, primary
key )?
Same question for users table.
Note: I'll have user role mapping table.

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



foreign key cautions

2008-06-17 Thread Sharique uddin Ahmed Farooqui
HI,
What are the caution should be taken when designing a db regarding foreign key?

I have "user" table , from which userid is taken as reference in many
tables, is it ok?

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



error creating table

2008-06-11 Thread Sharique uddin Ahmed Farooqui
Hi,
I'm trying to create a table  with 2 foreign keys from two tables, but
it not workin
here is the query ( i have generated this query from mysql workbench)
--
CREATE  TABLE IF NOT EXISTS `menutest`.`role_perm` (
  `idrole` INT NOT NULL ,
  `permid` VARCHAR(45) NOT NULL ,
  `status` BOOLEAN NOT NULL DEFAULT 1 ,
  INDEX fk_role (`idrole` ASC) ,
  INDEX fk_perm (`permid` ASC) ,
  CONSTRAINT `fk_role`
FOREIGN KEY (`idrole` )
REFERENCES `menutest`.`role` (`idrole` )
ON DELETE SET NULL
ON UPDATE CASCADE,
  CONSTRAINT `fk_perm`
FOREIGN KEY (`permid` )
REFERENCES `menutest`.`permission` (`idpermission` )
ON DELETE SET NULL
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- 
here is the error mssage :Can't create table
'.\menutest\role_perm.frm' (errno: 150)

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://safknw.blogspot.com/
"Peace" is the Ultimate thing we want.

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



Linq support

2008-01-31 Thread Sharique uddin Ahmed Farooqui
Hi,
Did latest Mysql .net provider (5.1.4) support Linq?

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and I are "the initiator".


mysql to mysql conversion tool

2008-01-29 Thread Sharique uddin Ahmed Farooqui
Hi,
I'm looking an application which let me convert one mysql to another db. I
need this to port my website from one cms (Vivvo) to another (Drupal).


-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and I are "the initiator".


monitoring utility

2007-10-31 Thread Sharique uddin Ahmed Farooqui
Hi,
I have a website running on Linux based cluster server. We don't have
access to logs, nor we have ssh access.
I want monitor mysql server usage of my site for the purpose of
optimizing my site scripts.
Pls suggest php based application.

-- 
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".

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



Fwd: Table crashing

2007-06-12 Thread Sharique uddin Ahmed Farooqui

Tables frequently crashing on my site. Here is the message I'm seeing
*
Warning*: Table './mydb/sessions' is marked as crashed and should be
repaired query: SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid=
s.uid WHERE s.sid = '854c6474111de174abbddf77f74b9d99' in *
/www/ims/includes/database**.mysqli.inc* on line *151*

I don't understand what's wrong with db.
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".


Table crashing

2007-06-12 Thread Sharique uddin Ahmed Farooqui

Tables frequently crashing on my site. Here is the message I'm seeing
*
Warning*: Table './mydb/sessions' is marked as crashed and should be
repaired query: SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid=
s.uid WHERE s.sid = '854c6474111de174abbddf77f74b9d99' in *
/www/ims/includes/database.mysqli.inc* on line *151*

I don't understand what's wrong with db.
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".


Re: CSV import

2007-05-28 Thread Sharique uddin Ahmed Farooqui

Both PhpMyAdmin and SQLYOG doesn't support customised mapping.
My cvs data structure is diff from mysql table. I just want to import values
for one field only.
Previously I was using Mysql Front but it crashes. Also this s/w very old
and discontinued.
Why mysql doesn't  implement it in MysqlAdmin?
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".

On 5/28/07, Geoffroy Cogniaux <[EMAIL PROTECTED]> wrote:


Hi,
It can be done easily with PhpMyAdmin, but it is not in .net

2007/5/28, Sharique uddin Ahmed Farooqui <[EMAIL PROTECTED]>:
>
> I want to import data from a CSV file in a  table. MySql admin doesn't
> support import from CSV files.
> Format of data is different from structure of table.
>
> Is there any app/snippet   written for this task in .net , which I can
> modify according to my need.
>
> --
> Sharique uddin Ahmed Farooqui
> (C++/C# Developer, IT Consultant)
> A revolution is about to begin.
> A world is about to change.
> And you and me are "the initiator".
>



CSV import

2007-05-27 Thread Sharique uddin Ahmed Farooqui

I want to import data from a CSV file in a  table. MySql admin doesn't
support import from CSV files.
Format of data is different from structure of table.

Is there any app/snippet   written for this task in .net , which I can
modify according to my need.

--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".


Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

2007-05-02 Thread Sharique uddin Ahmed Farooqui

I have upgraded a website from drupal 4.7.4 to drupal 5.1 (I have also moved
website from one server to another), now when I try to login I'm getting
following error.


user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' query: user_is_blocked SELECT
name FROM users WHERE status = 0 AND name = LOWER('user1') in
/www/mysite/includes/database.mysql.inc on line 172.

user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' query: user_load SELECT * FROM
users u WHERE LOWER(name) = LOWER('user1') AND pass =
'7b063a8b8aa2219449cb35f4e415295f' AND status = 1 in
/www/mysite/includes/database.mysql.inc on line 172.

Sorry, unrecognized username or password. Have you forgotten your password?

I have also posted this in drupal forum http://drupal.org/node/140303.
What I've done is I have exported data from old db via phpMyAdmin and
imported into new database.
Since it related to mysql so i'm posting here.
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://www.managefolio.net/news/
A revolution is about to begin.
A world is about to change.
And you and me are "the initiator".