mysql

information_schema shows "unknown table status: TABLE_TYPE" mysql phpmyadmin

I have phpmyadmin installed on linux web server. WHen i log into phpmyadmin using root user to see information_schema and click on table called triggers then at the bottom of table structure i see this line unknown table status: TABLE_TYPE Is that the error or something else ...

Setting up MySQL database

I do have single database and near about 11 tables. while my web page is opening informations from these 11 tables will be accessed same time. according to my current settings what I did now is for each table database is opening and closing. say I had given username and password to open databse for each table and close after retrieving i...

Spring Jdbc Template + MySQL = TransientDataAccessResourceException : Invalid Argument Value : Java.io.notSerializationException

I was using spring jdbc template to insert some data into the database and I was getting this error. Here is my code : JdbcTemplate insert = new JdbcTemplate(dataSource); for(ResultType result : response.getResultSet().getResult()) { Object[] args = new Object[] {result.getAddress(), result.getCity(), result...

Delphi / MySql : Problems escaping strings

N00b here, having problems escaping strings. I used the QuotedStr() function - shouldn't that be enough. Unfortunately, the string that I am trying to quote is rather messy, but I will post it here in case anyone wants to paste it into WinMerge or KDiff3, etc. I am trying to store an entire Delphi form into the database, rather than in...

How to create sql insert query dynamically in mysql

I am creating an application where I am generating pins dynamically based on user's input and storing them into mySql database. $sql = "INSERT INTO tblpin ('pinId', 'ownerId', 'usedby', 'status') VALUES for($i=0;$i<$npin;$i++) { ('$pin[$i]','$ownerid', 'Free', '1'); } ;"; how can I do that...

Casting a Calculated Column in a MySQL view.

I have a view that contains a calculated column. Is there are a way to cast it as a CHAR or VARCHAR rather than a VARBINARY ? Obviously, I have tried using CAST(... as CHAR) but it gives an error. Here is a simple replicable example. CREATE VIEW view_example AS SELECT concat_ws('_', lpad(9, 3,'0'), lpad(1,3,'0'), date_format(now(),'%Y...

PHP MySQL Select multiple tables

Is it posibble to select 3 tables at a time in 1 database? Table 1: employee --> employee_id --> first_name --> last_name --> middle_name --> birthdate --> address --> gender --> image --> salary Table 2: logs ...

Need help with SQL Query

Say I have 2 tables: Person - Id - Name PersonAttribute - Id - PersonId - Name - Value Further, let's say that each person had 2 attributes (say, gender and age). A sample record would be like this: Person->Id = 1 Person->Name = 'John Doe' PersonAttribute->Id = 1 PersonAttribute->PersonId = 1 PersonAttribute->Name = 'Gender...

Enum datatype versus table of data in MySQL?

I have one MySQL table, users, with the following columns: user_id (PK) email name password To manage a roles system, would there be a downside to either of the following options? Option 1: Create a second table called roles with three columns: role_id (Primary key), name, and description, then associate users.user_id with roles.ro...

MySQL Create tables without committing current transaction

I'd like my program to be able to install plugins, and rollback all the changes made if an error occurs. So I create a transaction that keeps all the things that were added while installing the plugin. The problem is that the plugin may want to create tables, and doing so automatically commits the current transaction in MySQL. See State...

login/logout problem in PHP

i have a question. I have a website in which i am giving security like login id and password( as usual). No what i want is that, 1) I don't want to allow a single user to login in different machine at the same time. 2) For this i am using a column in database which is keeping the current status of user(i.e. loging/logout). I am allowi...

How to enable propagation of create view operation to other mysql cluster node?

I have deployed a mysql cluster with 3 sql nodes. I create a NDB table in node1, then other nodes will create this table,too. I create a view in node1, then other nodes will not create this view. Now, my question is How to enable propagation of create view operation to other mysql cluster node? ...

SubSonic: MySqlDataReader closes connection.

I am using SubSonic 2.1 and entcountered a problem while executing a Transaction with SharedDbConnectionScope and TransactionScope. The problem is that in the obj.Save() method I get an "The connection must be valid and open" exception I tracked down the problem to this line: // Loads a SubSonic ActiveRecord object User user = new Use...

Data Modeling Help - Do I add another table, change existing table's usage, or something else?

Assume I have the following tables and relationships: Person - Id (PK) - Name A Person can have 0 or more pets: Pet - Id (PK) - PersonId (FK) - Name A person can have 0 or more attributes (e.g. age, height, weight): PersonAttribute _ Id (PK) - PersonId (FK) - Name - Value PROBLEM: I need to represent pet attributes, too. As it t...

Thinking sphinx not rendering results in order of 'relevance'

Thinking sphinx documentation says that it automatically sorts the results based on relevance. What is its relevance metric ? How can we define our own relevance metric for the system ? Example : I have a db in which one entry is Windows XP & it contains a reference to Microsoft. I also have an entry for Microsoft itself. So when I quer...

How to approach this SQL query

I have data related as follows: A table of Houses A table of Boxes (with an FK back into Houses) A table of Things_in_boxes (with an FK back to Boxes) A table of Owners (with an FK back into Houses) In a nutshell, a House has many Boxes, and each Box has many Things in it. In addition, each House has many Owners. If I know two Owne...

UIDs for data objects in MySQL

Hi there, I am using C++ and MySQL. I have data objects I want to persist to the database. They need to have a unique ID for identification purposes. The question is, how to get this unique ID? Here is what I came up with: 1) Use the auto_increment feature of MySQL. But how to get the ID then? I am aware that MySQL offers this "SELEC...

cakephp and SQL_CALC_FOUND_ROWS

I am trying to add the SQL_CALC_FOUND_ROWS into a query (Please note this isn't for pagination) please note I am trying to add this to a cakePHP query the code I currently have is below: return $this->find('all', array( 'conditions' => $conditions, 'fields'=>array('SQL_CALC_FOUND_ROWS','Category.*','COUN...

Mysql console slow on import of huge sql files

My import of sql via the mysql console is rather slow and as our sql file is increasing every day I would like to know if there are any alternatives on how to import a sql file faster. Changing to oracle or other systems is no option, the configuration has to stay the same. Currently the sql file is: 1.5 Gb I'm on Wamp with Apache 2.2....

Checking for reciprocal relationships in mysql. A trivial one table problem.

I have a mysql table that stores relationships. Items can be related to another item in one direction, or both items can be related to each other. I want to return all items related to my primary item - but I also want to check to see if the related item has a 'reverse relationship' to the current item and show this as a boolean |----...