Re: error creating table
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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".
