Problems reinstalling mySql


Hi all. I have problems reinstalling mySql. Two days ago I started to get problems setting up Datasources. The error stated that the connection to the database server had failed. When I tried to login in mySql I couldn't any longer so I decided to start from scratch but every time It doesn't pass the security settings. I get a 1045 error ("error nr. 1045 access denied for user@localhost (using password: no)") I have turned off the Windows Firewall temporary but that didn't do anything. What should I do to solve this

I use Coldfusion as my develping platform
Posted On: Monday 31st of December 2012 01:44:25 AM Total Views:  446
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




UDF problems

This is my first UDF ever. My goal is to take a date in the form 'YYYY-MM-DD HH:MM AM' and turn it into a UNIX timestamp. I am migrating data from a table that stores dates like this in the mediumtext, to a table that stores the date in unix timestamps. Anybody have any idea why I keep getting: #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 '' at line 3 Here's my UDF: DELIMITER $$ CREATE FUNCTION CONVERT_PHP_TIMESTAMP(field MEDIUMTEXT) RETURNS INT(11) BEGIN DECLARE field_date VARCHAR(45); DECLARE field_hour VARCHAR(45); DECLARE field_minutes VARCHAR(45); DECLARE field_timestamp INT(11); IF SUBSTRING(field, -2) = 'AM' THEN SET field_timestamp = SUBSTRING(field, -2); ELSEIF SUBSTRING(field, -2) = 'PM' THEN SET field_date = SUBSTRING(field FROM 0 FOR 10); SET field_hour = SUBSTRING(field FROM 11 FOR 2); SET field_minutes = SUBSTRING(field FROM 14 FOR 2); SET field_hour = CONVERT(INT, field_hour); SET field_hour = (field_hour + 12); SET field_timestamp = CONCAT(field_date,' ', field_hour, ':', field_minutes); END IF; RETURN(SELECT unix_timestamp(field_timestamp)); END $$ DELIMITER ;
VIEWS ON THIS POST

113

Posted on:

Thursday 25th October 2012
View Replies!

MYSQL problems

mysql> CREATE DATBASE -> ethermagicdb -> create database -> ethermagicdb -> -> -> -> \c mysql> create database emagicdb; Query OK, 1 row affected (0.33 sec) mysql> use emagicdb; Database changed mysql> create table jobseeker( -> jobseeker varchar(20) not null, -> password varchar(255) not null, -> lastname varchar(50) not null, -> street varchar(50) not null, -> citytown varchar(50) not null, -> postalzip varchar(50) not null, -> e-mail varchar(50) not null, -> phonemobile char(15) not null, -> fax char(15), -> ); ERROR 1064 (42000): 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 '-mail varchar(50) not null, phonemobile char(15) not null, fax char(15), )' at line 8 mysql> jobseeker varchar(20) not null, -> password varchar(255) not null, -> lastname varchar(50) not null, -> street varchar(50) not null, -> citytown varchar(50) not null, -> postalzip varchar(50) not null, -> c:\ -> c -> ccccccccccc/ -> c/ -> -> \c mysql> create table jobseeker( -> jobseeker varchar(20) not null, -> password varchar(255) not null, -> lastname varchar(50) not null, -> street varchar(50) not null, -> citytown varchar(50) not null, -> postalzip varchar(50) not null, -> email varchar(50) not null, -> phonemobile char(15) not null, -> fax char(15), -> ); ERROR 1064 (42000): 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 ')' at line 11 mysql> What have I done wrong lol Can anyone help me please
VIEWS ON THIS POST

128

Posted on:

Thursday 25th October 2012
View Replies!

last_isert_id() problems

Hi folks I'm having a problem with using last_insert_Id(). This is my query in coldfusion, everything between is mysql code exept for #Trim(FORM.country_name)# stuff which are form varialbes. #Trim(FORM.country_name)# = Cuba in form field, you get the idea. If I am using the wrong syntax as stated in the error message, could you show me the right syntax to use.
VIEWS ON THIS POST

194

Posted on:

Thursday 25th October 2012
View Replies!

Two (rather complex) problems. Help needed!

Problem 1 I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem. When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query: Code: select p.id as id, p.title as title, p.body as body, p.reltime as reltime, p.quota as quota, count(s1.id) as subcount_ns, from packs as p left outer join submissions as s1 on s1.pack = p.id where s1.state > -1 and p.released = 0 group by s1.pack order by p.id asc; How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0 I'm not sure how to do this, since I have already joined the submissions table to the packs table. The ratings table (sub_ratings) looks like this: Code: id | subid | userid | rating | time The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5. -------------------------------------------- Problem 2 For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example: 1,43,65 This would be an example of three users having collaborated on one design. Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission I'm looking for something along the lines of php's "explode" function, but in mysql. Is this possible
VIEWS ON THIS POST

229

Posted on:

Thursday 25th October 2012
View Replies!

Evaluationg mysql queries

What are some useful ways in order to check if the query is optimized and well written and in case it is not what are the common ways to point out parts that can be improved
VIEWS ON THIS POST

249

Posted on:

Monday 29th October 2012
View Replies!

Handler_read_rnd_next = 13k on SMALL db

Hi! I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not. One more thing: I can't search the forum for "Handler_read_rnd_next" because no search terms larger than 12 characters are allowed... Suggestions
VIEWS ON THIS POST

94

Posted on:

Monday 29th October 2012
View Replies!

Equi-join on a large table?

Hi , I'm running on MySQL 5.x Let's say I have the following table of data: Code: Table: test USER_ID | CLASS_ID 1 2 1 3 1 4 2 2 2 4 3 2 3 4 I'm trying get all the classes that these users have in common. E.g. If i ran a query to do this I would get class_id 2 and 4, because those are the only classes that all three users have in common. I think I want to do a equi-join, something like: Code: SELECT DISTINCT t1.class_id FROM test AS t1, test AS t2 WHERE t1.class_id = t2.class_id AND t1.user_id != t2.user_id I'm getting my results, but I'm wondering if this will scale at all to large amount of data. Here's the table and data code if anyone wants to play: Code: -- -- Table structure for table `test` -- CREATE TABLE `test` ( `user_id` int(11) NOT NULL, `class_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `test` -- INSERT INTO `test` (`user_id`, `class_id`) VALUES (1, 2), (1, 3), (1, 4), (2, 2), (2, 4), (3, 2), (3, 4);
VIEWS ON THIS POST

84

Posted on:

Monday 29th October 2012
View Replies!

Null Value causes LEFT JOIN to Fail

I'm having trouble with a multiple left join where a null or zero value in a field value causes the MySQL call and resulting PHP script to teminate. The MySQL call is as such: Code: $sql = "SELECT space_sales.*, companies.*, contacts.*, employees.first_name AS rep_first, employees.last_name AS rep_last FROM space_sales LEFT JOIN companies ON space_sales.company_id = companies.company_id LEFT JOIN contacts ON companies.contact_id = contacts.contact_id LEFT JOIN employees ON companies.account_rep = employees.employee_id WHERE sales_id = $sales_id "; The error message I get is: Quote: 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 '' at line 3 The problem is that the company being referenced does not have an assigned contact so the field "companies.contact_id" is Null. When I remove this join the error goes away. I thought that in a null join, the query would return only values from the left side of the join and the values from the right side would simply be null. Why is it failing in this case
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!

Is mysql causing firewall problems (FW-1 at fw01: Access denied)?

This seems unlikely to me, but I figured I'd ask anyway. For the last several years I have been running a phpbb forum with winxp, apache, php and mysql. None of my users has ever had trouble accessing it. Over the weekend, I tinkered with mysql to tighten up security. Specifically, I - created a mysql user just for the forum - granted that user database specific privs but not global privs - added skip-networking to my.ini to disallow all mysql tcp/ip connections and enabled pipes as described here: http://forums.devshed.com/mysql-help-4/properly-secuiring-msyql-333574.html ) I did not change my apache settings or any settings other than mysql settins. Now, I hear from a user that she can't get into the forum at her work and she is experiencing this error: Quote: FW-1 at smtlfw01: Access denied. I'm guessing her problem is unrelated to my mysql tinkering but I figured I'd ask here just to be sure. Any chance they are related
VIEWS ON THIS POST

192

Posted on:

Monday 29th October 2012
View Replies!

Storing the result from a sub query

Hi all! Introduction I am far from an SQL-expert but I have used MySQL for over two years now in various PHP-projects. In the current project I am working on, I am developing a betting-look-a-like game called PlingChallange. The MySQL version is 4.0.25 and it is, sadly, nothing I can do about it. The problem I am simply trying to sum up a total score from every game that the user choosed the correct sign(1, X or 2). Here is what I would like to use(but i doesent work) Code: UPDATE ChallengeUserTurn SET score = (SELECT sum( a.odds ) AS score FROM ChallengeUserGame a, PdpGameLangen b WHERE a.userId =3 AND a.langenTurnId = 53693592 AND a.langenGameId = b.id AND b.sign = a.sign) WHERE userId = 3 AND langenGameId = 53693592 Details Table ChallengeUserGame holds information of which sign the user choosed (1,X,2), the odds (eg. 1.85), user and turn (2 turns per week, 80 games per turn). Table ChallengeUserTurn holds, among other things, the total score for each user Table PdpGameLangen holds all kind of information about the game, but most importantly, the correct sign (1,X,2) What am I doing wrong I
VIEWS ON THIS POST

194

Posted on:

Monday 29th October 2012
View Replies!

Finding the code for a PREVIOUS button...

I'm getting crazy. There's a very simple table with the fields id (int 11), date (date) and text (text). I'm showing the last content on my site using this query: SELECT * FROM tab ORDER BY data DESC LIMIT 0,1 The page address for the first shown news is: news.phpid=CURRENTID (CURRENTID is the $row[0], the id of the last news, for example). Now, I have two html buttons: PREVIOUS and NEXT. For the NEXT button, I've already found the proper code: SELECT * FROM tab WHERE data
VIEWS ON THIS POST

70

Posted on:

Monday 29th October 2012
View Replies!

Sum of time ?

Got a column in my datebase and wish to get the sum of hours e.g. possible values 09:32:00 08:29:00 to give a result of 18:01:00 How do I go about this \t SUM gives me 176100 PS Im using PHP to minuplate data
VIEWS ON THIS POST

68

Posted on:

Monday 29th October 2012
View Replies!

Can anybody help me?

my question is: how mysql store data in frm, myd and myi files I want to know the structure about the frm file and so on. It just like sql server has .mdf file to store the data, but actually the data is stored in page. Every page is 8Kb. And the top 96bytes is the system information about the page. Are there the same concept in mysql
VIEWS ON THIS POST

146

Posted on:

Monday 29th October 2012
View Replies!

Is there ever a situation when it is good to store duplicate data?

I'm in the middle of an argument with a colleague about this and I need an expert opinion. My argument is that you should never store the same data or relationship in more than one location. His is that you can in circumstances where the performance increase is worth it. ---------------------------------- Here is the situation. Our site has many users (user_id). And many clubs (club_id). Users can be associated with a club in a variety of ways... Example A "supporter" links a user_id to a club_id. A "player" links a user_id (to a player_id to a team_id) to a club_id. Notice how the first one goes straight from user_id to club_id, and the second goes via the players and teams tables. At the moment, the data is stored poorly in separate tables. Code: link_supporters user_id | club_id Code: link_players user_id | player_id I wanted to store all this data into one table and came up with the following solution....... Code: members user_id | type | data_id the type can be anything I choose: "su" supporter, "pl" player etc etc and the data_id can be any id I choose. Example data for a supporter: user_id: 12, type: su, data_id: 47 data_id in this case is the club_id Example data for a player: user_id: 12, type: pl, data_id: 833 data_id in this case is a player_id --------------------- My colleague thinks that we should have an additional column. Code: members user_id | club_id | type | data_id So that all members of a club can be brought out quickly, but in some instances, club_id can be derived from the data_id, so I have said that this is a bad idea..... ----------- Our site has thousands of these relationships in our existing linking tables, so the solution needs to be fast and scalable. Are either of these methods valid
VIEWS ON THIS POST

82

Posted on:

Monday 29th October 2012
View Replies!

Did a stupid thing. I forgot the password to my Pixel script software. HELP!!!

Ok, before you laugh, let me explain. I contracted to have some PHP work done on my Pixel script site pixelusin.com . The guy was kinda flaky so I changed the password. My computer crashed and all of my hosting and password information was lost. Any ideas as to how I can get into the control panel short of starting over again
VIEWS ON THIS POST

79

Posted on:

Monday 29th October 2012
View Replies!

Copying data from one table to another

I could really use some help figuring out this query. I am trying to copy data from table to another. Usually I would use a simple INSERT INTO table column SELECT .... however I'm not sure how to do this with the way the tables are structured and the conditions that need to be met. Here are the Table names and their structures: Code: nucleus_plug_customfield: itemid | Field | Value ----------------------------------------------------------------------- 314 3 315 314 2 521 315 2 314 315 3 521 521 2 314 521 3 315 nucleus_item: inumber | idgame | irvocab | irdirty | iblog -------------------------------------------------------------------------- 314 field 3 value field 2 value NULL 2 521 field 3 value NULL field 2 value 3 315 NULL field 2 value field 3 value 4 I need to write a query(s) that does the following. For inumber = itemid ... IF iblog =2 COPY nucleus_plug_customfield.value TO nucleus_item.idgame WHERE field = 3 For inumber = itemid ... IF iblog =2 COPY nucleus_plug_customfield.value TO nucleus_item.irvocab WHERE field = 2 For inumber = itemid ... IF iblog =3 COPY nucleus_plug_customfield.value TO nucleus_item.idgame WHERE field = 3 For inumber = itemid ... IF iblog =3 COPY nucleus_plug_customfield.value TO nucleus_item.irdirty WHERE field = 2 For inumber = itemid ... IF iblog =4 COPY nucleus_plug_customfield.value TO nucleus_item.irvocab WHERE field = 2 For inumber = itemid ... IF iblog =4 COPY nucleus_plug_customfield.value TO nucleus_item.irdirty WHERE field = 3
VIEWS ON THIS POST

93

Posted on:

Monday 29th October 2012
View Replies!

Controlling no. pf records in a table

HI, Is it possible to specify the limit of records that can be entered in the table.
VIEWS ON THIS POST

71

Posted on:

Monday 29th October 2012
View Replies!

Generate table

I have a table called records it has 65,098 records I want to create a second table called backuprecords which will have 5000 records out of the 65,098 records can someone tell me how my sql query will be todd
VIEWS ON THIS POST

73

Posted on:

Saturday 10th November 2012
View Replies!

How do I make a table column auto increment??

Originally posted by : Blake Patterson (blake@blakespot.com) When creating a table, using MySQL, I was able to enter the field description as, say:unid not null auto_increment primary key, ...to set an ideal unid field that would necessarily be unique and would take care of incrementing itself. When entering this straight SQL command-line piece into MS SQL server, it does not like "auto_increment" or any variation of it. How do I set this option using MS SQL server\t
VIEWS ON THIS POST

75

Posted on:

Saturday 10th November 2012
View Replies!

Creating Job in SQL server Agent

I need to schedule the job on a specific time. That time is stored in a table of a database. How is this possible to get this time out and run the job. Thx, Kurt
VIEWS ON THIS POST

64

Posted on:

Monday 12th November 2012
View Replies!

Search across multiple fields

Is it possible to run a search for a single string that'd be a concatenation of the values in multiple fields. For instance with a few fields and values: id: 1 artist: John Mayer track: Who Says I am hoping to run a search for the string "John Mayer - Who Says" that will return this record. Possible
VIEWS ON THIS POST

73

Posted on:

Monday 19th November 2012
View Replies!

Sum issue in sql in an asp page

Hi all I'm having a difficult time. Here's the background. I have two tables with identical columns. TableA has sales # for today, TableB has historical sales # I am doing an asp page and have sql statements here and there that work. I am trying to combine a sql statement with a UNION ALL in a recordset. I'm getting the feeling I cannot Sum a sum in sql in asp. This works: rsSalesDenver.Source = "SELECT Sum(proddta.F554211B.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211B WHERE proddta.F554211B.SDEMCU LIKE '%DEN' Or (proddta.F554211B.SDEMCU LIKE '%FTN') AND (proddta.F554211B.SDADDJ BETWEEN "&vjStart&" AND "&vjEnd&") UNION ALL Select Sum(proddta.F554211A.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211A WHERE proddta.F554211A.SDEMCU LIKE '%DEN' Or (proddta.F554211A.SDEMCU LIKE '%FTN');" BUT..when I add the simple Sum to add the two sums..it just gives me a generic Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. /management/bi/newtest4.asp, line 393 And this is all I've added... rsSalesDenver.Source = "SELECT Sum(SumOfSDAEXP) AS SumOfSDAEXP FROM (Select Sum(proddta.F554211B.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211B WHERE proddta.F554211B.SDEMCU LIKE '%DEN' UNION ALL Select Sum(proddta.F554211A.SDAEXP) AS SumOfSDAEXP FROM proddta.F554211A WHERE proddta.F554211A.SDEMCU LIKE '%DEN');" Any ideas
VIEWS ON THIS POST

50

Posted on:

Tuesday 20th November 2012
View Replies!

SQL statement that doesn't work

Originally posted by : Nicolas (pub@nicolasbillaude.com)Usually, to find a recordset on databases I use a "SQL - LIKE" statement like this :mysql = "SELECT * FROM [abonnement] WHERE theme LIKE " & guil & pad & oForm("domaine") & pad & guil & ";"with : guil = Chr(39) ie ' pad = "%%"and it works very well. But now, I would like to change the statement like this :mysql = "SELECT * FROM [abonnement] WHERE theme = '" & oForm("domaine") "';"and the statement doesn't work at all.Is there someone than can help me Thanx in advanceNicolas------------------------------Nicolas Billaudhttp://www.nicolasbillaude.com
VIEWS ON THIS POST

58

Posted on:

Tuesday 20th November 2012
View Replies!

Important request help plz

Hi Its important How can I Attach Sql server data base by asp cose \t\t
VIEWS ON THIS POST

103

Posted on:

Monday 26th November 2012
View Replies!

Comparing Dates in SQL

! can anyone please help or had smiliar problems I have an Access database with a field named 'bid_close_date' (data type = 'Date/Time' - General Date: DD/MM/YYYY HH:MM:SS) in a table named tblplayers. I am running the following Query, using NOW() - which I was hopeing would return all the players from tblplayers whose bid_close_date is less than NOW(). SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;" HOWEVER! The SQL only seems to be looking at the first part of the Date i.e. DD and not the rest of the date string. i.e. MM/YYYY HH:MM:SS N.B. when I compare dates in VBscript it recognises the dates and the comparisons works. i.e. return the deed result. rs("Bid_close_date") THEN Response.Write("THIS AUCTION CLOSED etc...") End If %> If anyone has any suggestions I would be most grateful. David
VIEWS ON THIS POST

69

Posted on:

Monday 26th November 2012
View Replies!

Help with getting users and unique comments per widget

Given the three tables: WIDGETS id name 1 ABC Widget 2 XYZ Widget 3 123 Widget 4 789 Widget USERS id name 1 John Doe 2 Jane Doe 3 Mr. Rogers WIDGET_COMMENTS widget_id user_id comment 1 1 testing 2 1 a test 2 2 hello! 3 1 sample text 4 2 woohoo 4 2 goodbye! How could I compile a list of users and the number of widgets they, and only they, entered comments on. Example results: users.name # widgets John Doe 2 Jane Doe 1 Based on the data, widget 1 has 1 comment (by John Doe only), widget 2 has 2 comments (by John Doe and Jane Doe), widget 3 has 1 comment (by Jane Doe), and widget 4 has two comments (by John Doe). Since widget 2 has comments by two different users, it wouldn't be counted in the results. The other widgets only have comments by one specific user, so they would be counted in the results. Also, since Mr. Rogers had no comments, he shouldn't show up in the list.
VIEWS ON THIS POST

91

Posted on:

Sunday 30th December 2012
View Replies!