Search Results

Search found 18661 results on 747 pages for 'linq to mysql'.

Page 169/747 | < Previous Page | 165 166 167 168 169 170 171 172 173 174 175 176  | Next Page >

• MySQL performance - 100Mb ethernet vs 1Gb ethernet

  - by Rob Penridge

  Hi All I've just started a new job and noticed that the analysts computers are connected to the network at 100Mbps. The ODBC queries we run against the MySQL server can easily return 500MB+ and it seems at times when the servers are under high load the DBAs kill low priority jobs as they are taking too long to run. My question is this... How much of this server time is spent executing the request, and how much time is spent returning the data to the client? Could the query speeds be improved by upgrading the network connections to 1Gbps? (Updated for the why): The database in question was built to accomodate reporting needs and contains massive amounts of data. We usually work with subsets of this data at a granular level in external applications such as SAS or Excel, hence the reason for the large amounts of data being transmitted. The queries are not poorly structured - they are very simple and the appropriate joins/indexes etc are being used. I've removed 'query' from the Title of the post as I realised this question is more to do with general MySQL performance rather than query related performance. I was kind of hoping that someone with a Gigabit connection may be able to actually quantify some results for me here by running a query that returns a decent amount of data, then they could limit their connection speed to 100Mb and rerun the same query. Hopefully this could be done in an environment where loads are reasonably stable so as not to skew the results. If ethernet speed can improve the situation I wanted some quantifiable results to help argue my case for upgrading the network connections. Thanks Rob

  Read the article

• Mysql SELECT FOR UPDATE - strange issue

  - by Michal Fronczyk

  Hi, I have a strange issue (at least for me :)) with the MySQL's locking facility. I have a table: Create Table: CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 With this data: +----+ | id | +----+ | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 10 | | 11 | | 12 | +----+ Now I have 2 clients with these commands executed at the beginning: set autocommit=0; set session transaction isolation level serializable; begin; Now the most interesting part. The first client executes this query: (makes an intent to insert a row with id equal to 9) SELECT * from test where id = 9 FOR UPDATE; Empty set (0.00 sec) Then the second client does the same: SELECT * from test where id = 9 FOR UPDATE; Empty set (0.00 sec) My question is: Why the second client does not block ? An exclusive gap lock should have been set by the first query because FOR UPDATE have been used and the second client should block. If I am wrong, could somebody tell me how to do it correctly ? The MySql version I use is: 5.1.37-1ubuntu5.1 Thanks, Michal

  Read the article

• cakephp paginate using mysql SQL_CALC_FOUND_ROWS

  - by michael

  I'm trying to make Cakephp paginate take advantage of the SQL_CALC_FOUND_ROWS feature in mysql to return a count of total rows while using LIMIT. Hopefully, this can eliminate the double query of paginateCount(), then paginate(). http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows I've put this in my app_model.php, and it basically works, but it could be done better. Can someone help me figure out how to override paginate/paginateCount so it executes only 1 SQL stmt? /** * Overridden paginateCount method, to using SQL_CALC_FOUND_ROWS */ public function paginateCount($conditions = null, $recursive = 0, $extra = array()) { $options = array_merge(compact('conditions', 'recursive'), $extra); $options['fields'] = "SQL_CALC_FOUND_ROWS `{$this->alias}`.*"; Q: how do you get the SAME limit value used in paginate()? $options['limit'] = 1; // ideally, should return same rows as in paginate() Q: can we somehow get the query results to paginate directly, without the extra query? $cache_results_from_paginateCount = $this->find('all', $options); /* * note: we must run the query to get the count, but it will be cached for multiple paginates, so add comment to query */ $found_rows = $this->query("/* do not cache for {$this->alias} */ SELECT FOUND_ROWS();"); $count = array_shift($found_rows[0][0]); return $count; } /** * Overridden paginate method */ public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) { $options = array_merge(compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive'), $extra); Q: can we somehow get $cache_results_for_paginate directly from paginateCount()? return $cache_results_from_paginateCount; // paginate in only 1 SQL stmt return $this->find('all', $options); // ideally, we could skip this call entirely }

  Read the article

• MySQL Relational Database Foreign Key

  - by user623879

  To learn databasing, I am creating a movie database. To associate multiple directors with a movie, I have the following schema: movie(m_ID, ....) m_director(dirID, dirName)//dirID is a autoincrement primary key m_directs(dirID, m_ID) //dirID, m_ID are set as foreign Keys in the mysql database(InnoDB engine) I have a program that connects to the db that needs to add a movie to the database. I can easily add a new entry to the movie table and the m_director table, but I am having trouble adding a entry in the m_directs table. INSERT INTO m_director (dirName) VALUES("Jason Reitman"); INSERT INTO m_directs (dirID, m_ID) VALUES(LAST_INSERT_ID(), "tt0467406"); I am using this sql statement to insert a new director and add the association to the movie. I know the primary key of the movie, but I don't know the dirID, so I use LAST_INSERT_ID() to get the last id of the director just inserted. The problem I am having is that I get the following error: MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (`siteproducts`. `m_directs`, CONSTRAINT `m_directs_ibfk_2` FOREIGN KEY (`dirID`) REFERENCES `m_directs` (`dirID`) ON DELETE CASCADE ON UPDATE CASCADE) Any ideas?

  Read the article

• converting mysql database to sql server

  - by every_answer_gets_a_point

  i have a mysql database: /* MySQL Data Transfer Source Host: 10.0.0.5 Source Database: jnetdata Target Host: 10.0.0.5 Target Database: jnetdata Date: 5/26/2009 12:27:33 PM */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for chavrusas -- ---------------------------- CREATE TABLE `chavrusas` ( `id` int(11) NOT NULL auto_increment, `date_created` datetime default NULL, `luser_id` int(11) default NULL, `ruser_id` int(11) default NULL, `luser_type` varchar(50) default NULL, `ruser_type` varchar(50) default NULL, `SessionDay` varchar(250) default NULL, `SessionTime` datetime default NULL, `WeeklyReminder` tinyint(1) NOT NULL default '0', `reminder_phone` tinyint(1) NOT NULL default '0', `calling_card` varchar(50) default NULL, `active` tinyint(1) NOT NULL default '0', `notes` mediumtext, `ended` tinyint(1) NOT NULL default '0', `end_date` datetime default NULL, `initiated_by_student` tinyint(1) NOT NULL default '0', `initiated_by_volunteer` tinyint(1) NOT NULL default '0', `student_general_reason` varchar(50) default NULL, `volunteer_general_reason` varchar(50) default NULL, `student_reason` varchar(250) default NULL, `volunteer_reason` varchar(250) default NULL, `student_nli` tinyint(1) NOT NULL default '0', `volunteer_nli` tinyint(1) NOT NULL default '0', `jnet_initiated` tinyint(1) default '0', `belongs_to` varchar(50) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5913 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for tbluseravailability -- ---------------------------- CREATE TABLE `tbluseravailability` ( `availability_id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `weekday_id` int(11) NOT NULL, `timeslot_id` int(11) NOT NULL, PRIMARY KEY (`availability_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10865 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for tblusers -- ---------------------------- CREATE TABLE `tblusers` ( `id` int(11) NOT NULL auto_increment, `password` varchar(50) default NULL, `title` varchar(255) default NULL, `first` varchar(255) default NULL, `last` varchar(255) default NULL, `gender` varchar(255) default NULL, `address` varchar(255) default NULL, `address_2` varchar(255) default NULL, `city` varchar(255) default NULL, `state` varchar(255) default NULL, `postcode` varchar(255) default NULL, `country` varchar(255) default NULL, `email` varchar(255) default NULL, `emailnotes` varchar(255) default NULL, `Home_Phone` varchar(255) default NULL, `Office_Phone` varchar(255) default NULL, `Cell_Phone` varchar(255) default NULL, `Contact_Preference` varchar(255) default NULL, `Birthdate` datetime default NULL, `Age` varchar(255 and it goes on for about 10mb i need to convert it to ms sql, how do i do it?

  Read the article

• OpenStreetMap Proximity search using mySQL

  - by Chris

  Hi, I'm just playing around with a dataset of my region generated by JOSM. I moved it into a mySQL DB with the 0.6 API scheme using Osmosis and now I'm desperately trying the following: I want to get all streets of a city. AFAIK there is no tag/relation in the OSM data to determine this so I tried it using a proximity search to get all nodes in a radius around a node representing the city center. Most of the time I looked at the approaches here: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula What I got is the following SQL code that should get me the closest 100 nodes around the node with id 36187002 and within a radius of 10km. set @nodeid = 36187002; set @dist = 10; select longitude, latitude into @mylon, @mylat from nodes where id=@nodeid limit 1; SELECT id, ( 6371 * acos( cos( radians(@mylon) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(@mylat) ) + sin( radians(@mylon) ) * sin( radians( latitude ) ) ) ) AS distance FROM nodes HAVING distance < @dist ORDER BY distance LIMIT 0 , 100; Well.. it doesn't work. :( I guess the main problem is that OSM lats/lons are multiplied by 10.000.000 and I don't know how I can correct this function to make it work. Any ideas about this? All solutions/alternatives are very welcome! Have a nice weekend! Chris

  Read the article

• Mysql german accents not-sensitive search in full-text searches

  - by lukaszsadowski

  Let`s have a example hotels table: CREATE TABLE `hotels` ( `HotelNo` varchar(4) character set latin1 NOT NULL default '0000', `Hotel` varchar(80) character set latin1 NOT NULL default '', `City` varchar(100) character set latin1 default NULL, `CityFR` varchar(100) character set latin1 default NULL, `Region` varchar(50) character set latin1 default NULL, `RegionFR` varchar(100) character set latin1 default NULL, `Country` varchar(50) character set latin1 default NULL, `CountryFR` varchar(50) character set latin1 default NULL, `HotelText` text character set latin1, `HotelTextFR` text character set latin1, `tagsforsearch` text character set latin1, `tagsforsearchFR` text character set latin1, PRIMARY KEY (`HotelNo`), FULLTEXT KEY `fulltextHotelSearch` (`HotelNo`,`Hotel`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`,`HotelText`,`HotelTextFR`,`tagsforsearch`,`tagsforsearchFR`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci; In this table for example we have only one hotel with Region name = "Graubünden" (please note umlaut ü character) And now I want to achieve same search match for phrases: 'graubunden' and 'graubünden' This is simple with use of MySql built in collations in regular searches as follows: SELECT * FROM `hotels` WHERE `Region` LIKE CONVERT(_utf8 '%graubunden%' USING latin1) COLLATE latin1_german1_ci This works fine for 'graubunden' and 'graubünden' and as a result I receive proper result, but problem is when we make MySQL full text search Whats wrong with this SQL statement?: SELECT * FROM hotels WHERE MATCH (`HotelNo`,`Hotel`,`Address`,`City`,`CityFR`,`Region`,`RegionFR`,`Country`,`CountryFR`, `HotelText`, `HotelTextFR`, `tagsforsearch`, `tagsforsearchFR`) AGAINST( CONVERT('+graubunden' USING latin1) COLLATE latin1_german1_ci IN BOOLEAN MODE) ORDER BY Country ASC, Region ASC, City ASC This doesn`t return any result. Any ideas where the dog is buried ?

  Read the article

• excel:mysql: rs.Update crashes

  - by every_answer_gets_a_point

  i am connecting to mysql from excel and updating a table. as soon as i get to .update (rs.update) excel crashes. am i doing something wrong? Option Explicit Dim oConn As ADODB.Connection Private Sub ConnectDB() Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=pas;" & _ "Option=3" End Sub Function esc(txt As String) esc = Trim(Replace(txt, "'", "\'")) End Function Private Sub InsertData() Dim dpath, atime, rtime, lcalib, aname, rname, bstate, instrument As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ConnectDB With wsBooks rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable Worksheets.Item("Report 1").Select dpath = Trim(Range("B2").Text) atime = Trim(Range("B3").Text) rtime = Trim(Range("B4").Text) lcalib = Trim(Range("B5").Text) aname = Trim(Range("B6").Text) rname = Trim(Range("B7").Text) bstate = Trim(Range("B8").Text) ' instrument = GetInstrFromXML(wbBook.FullName) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = bstate ' .Fields("instrument") = "NA" .Update ' stores the new record End With ' get the last id Set rs = oConn.Execute("SELECT @@identity", , adCmdText) 'MsgBox capture_id rs.Close Set rs = Nothing End With End Sub

  Read the article

• Preparing a MySQL INSERT/UPDATE statement with DEFAULT values

  - by Raveren

  Quoting MySQL INSERT manual - same goes for UPDATE: Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list. So in short if I write INSERT INTO table1 (column1,column2) values ('value1',DEFAULT); A new row with column2 set as its default value - whatever it may be - is inserted. However if I prepare and execute a statement in PHP: $statement = $pdoObject-> prepare("INSERT INTO table1 (column1,column2) values (?,?)"); $statement->execute(array('value1','DEFAULT')); The new row will contain 'DEFAULT' as its text value - if the column is able to store text values. Now I have written an abstraction layer to PDO (I needed it) and to get around this issue am considering to introduce a const DEFAULT_VALUE = "randomstring"; So I could execute statements like this: $statement->execute(array('value1',mysql::DEFAULT_VALUE)); And then in method that does the binding I'd go through values that are sent to be bound and if some are equal to self::DEFAULT_VALUE, act accordingly. I'm pretty sure there's a better way to do this. Has someone else encountered similar situations?

  Read the article

• MySQL Connection Error in PHP

  - by user309381

  I have set the password for root and grant all privileges for root. Why does it say it is denied? ****mysql_query() [function.mysql-query]: Access denied for user 'SYSTEM'@'localhost' (using password: NO) in C:\wamp\www\photo_gallery\includes\database.php on line 56 Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in C:\wamp\www\photo_gallery\includes\database.php on line 56 The Query has problemAccess denied for user 'SYSTEM'@'localhost' (using password: NO) Code as follows: <?php include("DB_Info.php"); class MySQLDatabase { public $connection; function _construct() { $this->open_connection(); } public function open_connection() { /* $DB_SERVER = "localhost"; $DB_USER = "root"; $DB_PASS = ""; $DB_NAME = "photo_gallery";*/ $this->connection = mysql_connect($DBSERVER,$DBUSER,$DBPASS); if(!$this->connection) { die("Database Connection Failed" . mysql_error()); } else { $db_select = mysql_select_db($DBNAME,$this->connection); if(!$db_select) { die("Database Selection Failed" . mysql_error()); } } } function mysql_prep($value) { if (get_magic_quotes_gpc()) { $value = stripslashes($value); } // Quote if not a number if (!is_numeric($value)) { $value = "'" . mysql_real_escape_string($value) . "'"; } return $value; } public function close_connection() { if(isset($this->connection)) { mysql_close($this->connection); unset($this->connection); } } public function query($sql) { //$sql = "SELECT*FROM users where id = 1"; $result = mysql_query($sql); $this->confirm_query($result); //$found_user = mysql_fetch_assoc($result); //echo $found_user; return $found_user; } private function confirm_query($result) { if(!$result) { die("The Query has problem" . mysql_error()); } } } $database = new MySQLDatabase(); ?>

  Read the article

• Re-Convert timestamp DATE back to original format (when editing) PHP MySQL

  - by Jess

  Ok so I have managed to get the format of the date presented in HTML (upon display) to how I want (dd/mm/yyy)...However, the user also can change the date via a form. So this is how it's setup as present. Firstly, the conversion from YYYY-MM-DD to DD/MM/YYYY and then display in HTML: $timestamp = strtotime($duedate); echo date('d/m/Y', $timestamp); Now when the user selects to update the due date, the value already stored value is drawn up and presented in my text field (using exactly the same code as above).. All good so far. But when my user runs the update script (clicks submit), the new due date is not being stored, and in my DB its seen as '0000-00-00'. I know I need to convert it back to the correct format that MySQL wants to have it in, in order for it to be saved, but I'm not sure on how to do this. This is what I have so far in my update script (for the due date): $duedate = $_POST['duedate']; $timestamp = strtotime($duedate); date('Y/m/d', $timestamp); $query = "UPDATE films SET filmname= '".$filmname."', duedate= '".$duedate; Can somebody please point me in the right direction to have the new date, when processed, converted back to the accepted format by MySQL. I appreciate any help given! Thanks.

  Read the article

• Is there a problem with this MySQL Query?

  - by ThinkingInBits

  http://pastie.org/954073 The echos at the top all display valid data that fit the database schema. There are no connection errors Any ideas? Thanks in advance! Here is the echo'ed query INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES (1, 'r', 1, 'sdf', 'sdf', '2', 'd', 'd', '3', 'asdfasdfdf', 'df', '10 May 10', '10 May 10') MySQL is giving: #1064 - 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 'condition, stock_num, information, description, created, modified) VALUES (1, 'r' at line 1 id int(11) unsigned NO PRI NULL auto_increment Edit Delete cat_id int(11) unsigned NO NULL Edit Delete prod_name varchar(255) YES NULL Edit Delete prod_year varchar(10) YES NULL Edit Delete manufacturer varchar(255) YES NULL Edit Delete model varchar(255) YES NULL Edit Delete price varchar(10) YES NULL Edit Delete location varchar(255) YES NULL Edit Delete condition varchar(25) YES NULL Edit Delete stock_num varchar(128) YES NULL Edit Delete information text YES NULL Edit Delete description text YES NULL Edit Delete created varchar(20) YES NULL Edit Delete modified varchar(20) YES NULL Query: INSERT INTO equipment (cat_id, prod_name, prod_year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES (1, 'asdf', '234', 'adf', 'asdf', '34', 'asdf', 'asdf', '234', 'asdf', 'asdf', '10 May 10', '10 May 10')

  Read the article

• Can't select data from MySQL database: java.lang.NullPointerException

  - by Devel

  Hi, I'm trying to select data from database using this code: //DATABASE ResultSet rs; String polecenie; Statement st; String[] subj; public void polacz() { try { Class.forName("com.mysql.jdbc.Driver"); Connection pol=DriverManager.getConnection("jdbc:mysql://localhost:3306/testgenerator", "root", "pospaz"); st = pol.createStatement(); lblPolaczonoZBaza.setText("Polaczono z baza danych testgenerator"); } catch (Exception ek) { statusMessageLabel.setText("Can't connect to d: "+ek); } polecenie = "select * from subjects"; try { rs = st.executeQuery(polecenie); int i=0; while (rs.next()){ subj[i] = rs.getString("name"); i++; } st.close(); } catch (Exception ek) { statusMessageLabel.setText("Can't select data: "+ek); } } The second catch shows exception: java.lang.NullPointerException I looked everywhere and I can't find the solution. I'd be grateful for any help.

  Read the article

• Rolling Back a Transaction with MySQL Connector in VB.net

  - by Jonathan

  Hey all- I have one multi-row INSERT statement (300 or so sets of values) that I would like to commit to the MySQL database in an all-or-nothing fashion. insert into table VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); In some cases, a set of values in the command will not meet the criteria of the table (duplicate key, for example). When that happens I do not want any of the previous sets added to the database. I've implemented this with the following code, however, my rollback command doesn't appear to be making a difference. I've used this documentation: http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqltransaction.html Dim transaction As MySqlTransaction = sqlConnection.BeginTransaction() sqlCommand = New MySqlCommand(insertStr, sqlConnection, transaction) Try sqlCommand.ExecuteNonQuery() Catch ex As Exception writeToLog("EXCEPTION: " & ex.Message & vbNewLine) writeToLog("Could not execute " & sqlCmd & vbNewLine) Try transaction.Rollback() writeToLog("All statements were rolled back." & vbNewLine) Return False Catch rollbackEx As Exception writeToLog("EXCEPTION: " & rollbackEx.Message & vbNewLine) writeToLog("All statements were not rolled back." & vbNewLine) Return False End Try End Try transaction.commit() I get the DUPLICATE KEY exception thrown, no Rollback Exception thrown, and every set of values up to duplicate key committed to the database. What am I doing wrong? Thanks- Jonathan

  Read the article

• Masspay and MySql

  - by Mike

  Hi, I am testing Paypal's masspay using their 'MassPay NVP example' and I having difficulty trying to amend the code so inputs data from my MySql database. Basically I have user table in MySql which contains email address, status of payment (paid,unpaid) and balance. CREATE TABLE `users` ( `user_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(100) collate latin1_general_ci NOT NULL, `status` enum('unpaid','paid') collate latin1_general_ci NOT NULL default 'unpaid', `balance` int(10) NOT NULL default '0', PRIMARY KEY (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci Data : 1 [email protected] paid 100 2 [email protected] unpaid 11 3 [email protected] unpaid 20 4 [email protected] unpaid 1 5 [email protected] unpaid 20 6 [email protected] unpaid 15 I then have created a query which selects users with an unpaid balance of $10 and above : $conn = db_connect(); $query=$conn->query("SELECT * from users WHERE balance >='10' AND status = ('unpaid')"); What I would like to is for each record returned from the query for it to populate the code below: Now the code which I believe I need to amend is as follows: for($i = 0; $i < 3; $i++) { $receiverData = array( 'receiverEmail' => "[email protected]", 'amount' => "example_amount",); $receiversArray[$i] = $receiverData; } However I just can't get it to work, I have tried using mysqli_fetch_array and then replaced "[email protected]" with $row['email'] and "example_amount" with row['balance'] in various methods of coding but it doesn't work. Also I need it to loop to however many rows that were retrieved from the query as <3 in the for loop above. So the end result I am looking for is for the $nvpStr string to pass with something like this: $nvpStr = "&EMAILSUBJECT=test&RECEIVERTYPE=EmailAddress&CURRENCYCODE=USD&[email protected]&L_Amt=11&[email protected]&L_Amt=11&[email protected]&L_Amt=20&[email protected]&L_Amt=20&[email protected]&L_Amt=15"; Thanks

  Read the article

• Mysql error in stored procudure

  - by devuser

  This stored procedure is to search through all tables and columns in database. DELIMITER $$ DROP PROCEDURE IF EXISTS get_table $$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `auradoxdb`.`get_table`(in_search varchar(50)) READS SQL DATA BEGIN DECLARE trunc_cmd VARCHAR(50); DECLARE search_string VARCHAR(250); DECLARE db,tbl,clmn CHAR(50); DECLARE done INT DEFAULT 0; DECLARE COUNTER INT; DECLARE table_cur CURSOR FOR SELECT concat(SELECT COUNT(*) INTO @CNT_VALUE FROM `’,table_schema,’`.`’,table_name,’` WHERE `’, column_name,’` REGEXP ”’,in_search,”’) ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN (‘information_schema’,'test’,'mysql’); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; # #Truncating table for refill the data for new search. PREPARE trunc_cmd FROM “TRUNCATE TABLE temp_details;” EXECUTE trunc_cmd ; OPEN table_cur; table_loop:LOOP FETCH table_cur INTO search_string,db,tbl,clmn; # #Executing the search SET @search_string = search_string; SELECT search_string; PREPARE search_string FROM @search_string; EXECUTE search_string; SET COUNTER = @CNT_VALUE; SELECT COUNTER; IF COUNTER>0 THEN # # Inserting required results from search to tablehhh INSERT INTO temp_details VALUES(db,tbl,clmn); END IF; IF done=1 THEN LEAVE table_loop; END IF; END LOOP; CLOSE table_cur; # #Finally Show Results SELECT * FROM temp_details; END $$ DELIMITER ; Following error occurs when execute this.. Error Code : 1064 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 'SELECT COUNT(*) INTO @CNT_VALUE FROM `’,table_schema,’`.`’,table_name,’`' at line 12 (0 ms taken) could any body please help me to solve this?

  Read the article

• How should I design my MYSQL table/s?

  - by yaya3

  I built a really basic php/mysql site for an architect that uses one 'projects' table. The website showcases various projects that he has worked on. Each project contained one piece of text and one series of images. Original projects table (create syntax): CREATE TABLE `projects` ( `project_id` int(11) NOT NULL auto_increment, `project_name` text, `project_text` text, `image_filenames` text, `image_folder` text, `project_pdf` text, PRIMARY KEY (`project_id`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; The client now requires the following, and I'm not sure how to handle the expansions in my DB. My suspicion is that I will need an additional table. Each project now have 'pages'. Pages either contain... One image One "piece" of text One image and one piece of text. Each page could use one of three layouts. As each project does not currently have more than 4 pieces of text (a very risky assumption) I have expanded the original table to accommodate everything. New projects table attempt (create syntax): CREATE TABLE `projects` ( `project_id` int(11) NOT NULL AUTO_INCREMENT, `project_name` text, `project_pdf` text, `project_image_folder` text, `project_img_filenames` text, `pages_with_text` text, `pages_without_img` text, `pages_layout_type` text, `pages_title` text, `page_text_a` text, `page_text_b` text, `page_text_c` text, `page_text_d` text, PRIMARY KEY (`project_id`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; In trying to learn more about MYSQL table structuring I have just read an intro to normalization and A Simple Guide to Five Normal Forms in Relational Database Theory. I'm going to keep reading! Thanks in advance

  Read the article

• importing a large txt file in MySQL ?

  - by Taz

  Hi I am loading a text data in MySQL using the following command 'mysql> Load Data local Infile 'C:\\Documents and Settings\\Scan\\My Documents\\D ownloads\\instance_types_en.nt\\Copy of instance_types_en.txt' into table dbpedi aentities.resources fields terminated by ' ' lines terminated by 'rn';' Data is like (actually there is a newline after '.') <a> <b> <c> . <a> <b> <c> . <a> <b> <c> . <a> <b> <c> .<a> <b> <c> . <a> <b> <c> . Table has and auto increment ID field and then text fields for all three values. File size is about 750MB The problems are 1. appears to be in first text field 2. only 2MB data is imported

  Read the article

• Can't connect to MySql database from server using Devart

  - by annelie

  Hello, I'm having trouble connecting to a MySql database from a server. I'm using Devart to connect, and I'm not sure if I need to do something more than to simply reference the dlls. When I started working on this project it was referencing the CoreLabs dlls, which if I've understood is the previous name for Devart. That didn't work, so I downloaded the new Devart dlls instead. It works on my local machine, but when uploading to the server it crashes. I made a tiny console app to test the connection, and it fails when initialising, before I've even assigned a host etc. Do I need to do anything more than just upload my .exe file to the server? using System; using System.Collections.Generic; using System.Linq; using System.Text; using Devart.Data; using Devart.Data.MySql; namespace TestDatabaseConnection { public class Program { public static void Main(string[] args) { Console.WriteLine("about to connect"); ConnectToDatabase(); Console.ReadLine(); } public static void ConnectToDatabase() { MySqlConnection connection = new MySqlConnection(); } } } UPDATE: I can't see what the error is, I had a try catch around MySqlConnection connection = new MySqlConnection(); but no exception is thrown, it just crashes. Thanks, Annelie

  Read the article

• MySQL - optimising selection across two linked tables

  - by user293594

  I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with trans.A 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables: CREATE TABLE ij SELECT i,j FROM trans WHERE A30.; CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij; SELECT energy from states,temp WHERE id=temp.i; This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans: CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A30. UNION SELECT DISTINCT j FROM trans WHERE A30.; it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs. Many thanks, Christian

  Read the article

• MySQL INJECTION Solution...

  - by Val

  I have been bothered for so long by the MySQL injections and was thinking of a way to eliminate this problem all together. I have came up with something below hope that many people will find this useful. The only Draw back I can think of this is the partial search: Jo =returns "John" by using the like %% statement. Here is a php solution: <?php function safeQ(){ $search= array('delete','select');//and every keyword... $replace= array(base64_encode('delete'),base64_encode('select')); foreach($_REQUEST as $k=>$v){ str_replace($search, $replace, $v); } } foo(); function html($str){ $search= array(base64_encode('delete'),base64_encode('select')); $replace= array('delete','select');//and every keyword... str_replace($search, $replace, $str); } //example 1 ... ... $result = mysql_fetch_array($query); echo html($result[0]['field_name']); //example 2 $select = 'SELECT * FROM safeQ($_GET['query']) '; //example 3 $insert = 'INSERT INTO .... value(safeQ($_GET['query']))'; ?> I know, I know that you still could inject using 1=1 or any other type of injections... but this I think could solve half of your problem so the right mysql query is executed. So my question is if anyone can find any draw backs on this then please feel free to comment here. PLEASE GIVE AN ANSWER only if you think that this is a very useful solution and no major drawbacks are found OR you think is a bad idea all together...

  Read the article

• MySQL FULLTEXT not working

  - by Ross

  I'm attempting to add searching support for my PHP web app using MySQL's FULLTEXT indexes. I created a test table (using the MyISAM type, with a single text field a) and entered some sample data. Now if I'm right the following query should return both those rows: SELECT * FROM test WHERE MATCH(a) AGAINST('databases') However it returns none. I've done a bit of research and I'm doing everything right as far as I can tell - the table is a MyISAM table, the FULLTEXT indexes are set. I've tried running the query from the prompt and from phpMyAdmin, with no luck. Am I missing something crucial? UPDATE: Ok, while Cody's solution worked in my test case it doesn't seem to work on my actual table: CREATE TABLE IF NOT EXISTS `uploads` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `size` int(11) NOT NULL, `type` text NOT NULL, `alias` text NOT NULL, `md5sum` text NOT NULL, `uploaded` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; And the data I'm using: INSERT INTO `uploads` (`id`, `name`, `size`, `type`, `alias`, `md5sum`, `uploaded`) VALUES (1, '04 Sickman.mp3', 5261182, 'audio/mp3', '1', 'df2eb6a360fbfa8e0c9893aadc2289de', '2009-07-14 16:08:02'), (2, '07 Dirt.mp3', 5056435, 'audio/mp3', '2', 'edcb873a75c94b5d0368681e4bd9ca41', '2009-07-14 16:08:08'), (3, 'header_bg2.png', 16765, 'image/png', '3', '5bc5cb5c45c7fa329dc881a8476a2af6', '2009-07-14 16:08:30'), (4, 'page_top_right2.png', 5299, 'image/png', '4', '53ea39f826b7c7aeba11060c0d8f4e81', '2009-07-14 16:08:37'), (5, 'todo.txt', 392, 'text/plain', '5', '7ee46db77d1b98b145c9a95444d8dc67', '2009-07-14 16:08:46'); The query I'm now running is: SELECT * FROM `uploads` WHERE MATCH(name) AGAINST ('header' IN BOOLEAN MODE) Which should return row 3, header_bg2.png. Instead I get another empty result set. My options for boolean searching are below: mysql> show variables like 'ft_%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | +--------------------------+----------------+ 5 rows in set (0.02 sec) "header" is within the word length restrictions and I doubt it's a stop word (I'm not sure how to get the list). Any ideas?

  Read the article

• Using Partitions for a large MySQL table

  - by user293594

  An update on my attempts to implement a 505,000,000-row table on MySQL on my MacBook Pro: Following the advice given, I have partitioned my table, tr: i UNSIGNED INT NOT NULL, j UNSIGNED INT NOT NULL, A FLOAT(12,8) NOT NULL, nu BIGINT NOT NULL, KEY (nu), key (A) with a range on nu. nu ought to be a real number, but because I only have 6-d.p. accuracy and the maximum value of nu is 30000. I multiplied it by 10^8 made it a BIGINT - I gather one can't use FLOAT or DOUBLE values to PARTITION a MySQL table. Anyway, I have 15 partitions (p0: nu<25,000,000,000, p1: nu<50,000,000,000, etc.). I was thinking that this should speed up a typical to SELECT: SELECT * FROM tr WHERE nu>95000000000 AND nu<100000000000 AND A.>1. to something of the order of the same query on a table consisting of only the data in the relevant partition (<30 secs). But it's taking 30mins+ to return rows for queries within a partition and double that if the query is for rows spanning two (contiguous) partitions. I realise I could just have 15 different tables, and query them separately, but is there a way to do this 'automatically' with partitions? Has anyone got any suggestions?

  Read the article

• Asp.net renders string with wrong encoding, but PHP doesn't (MySQL)

  - by citronas

  I took over some old php application with MySQL as database. Inside the database, there are tables including content with localized strings (therefore containing special chars) Currently there is a PHP application accessing that database. My job is to create an ASP.net (C# codebehind) application that accesses that strings as well. That works, as far as encoding goes. If I try to access these strings, I do get a kind of encoding problem, like 'Ändern' and 'Prüfzeichen', but only in the ASP.net application. The PHP app sets utf-8 as charset and the strings are perfectly rendered. In the ASP.net application it's gibberish, regardless of the page encoding. In the MySQL database, the charset for the specified table 'translations' is set to 'latin --cp1252 West European' and collation to 'latin_swedish_ci'. I can't seem to figure out what PHP apparently does, and ASP.net does not. I traced the php code and could not find any sign of special encoding while getting a string from the database. The question is, how can I ensure correct encoding inside the ASP.net application without modifying the database, because big changes at the php code are not possible? Does anybody have a clue?

  Read the article

• Changing character encoding in MySQL, PHP scripts, HTML

  - by Sandman

  So, I have built on this system for quite some time, and it is currently outputting Latin1 (ISO-8859-1) to the web browser, and this is the components: MySQL - all data is stored with the Latin1 character set PHP - All PHP text files are stored on disk with Latin1 encoding HTML - The output has the http-equiv="content-type" content="text/html; charset=iso-8859-1" meta tag So, I'm trying to understand how the encoding of the different parts come into play in my workflow. If I open a PHP script and change its encoding within the text editor to UTF-8 and save it back to disk and reload the web browser, the text is all messed up - unless the text comes from the DB. If I change the encoding of the DB to UTF-8 and keep the PHP files in latin1 I have to use utf8_decode() for the data to display correctly. And if I change the HTML code the browser will read it incorrectly. So yeah, I realise that if I want to "upgrade" to UTF8, I have to update all three parts of this setup for it to work correctly, but since it's a huge system with some 180k lines of PHP code and millions of posts in a lot of databases/tables, I don't want to start something like this without understanding everything correctly. What haven't I thought about? What could mess this up beyond fixing? What are the procedures for changing the encoding of an entire MySQL installation and what's the easiest way to change the encoding of hundreds or thousands of PHP files on disk? The META tag is luckily added dynamically, so I'll change that in one place only :) Let me hear about your experiences with this.

  Read the article

< Previous Page | 165 166 167 168 169 170 171 172 173 174 175 176  | Next Page >