-
How To Test Master-Master / Master-Slave / Standard / Circular Replication on MySQL
Date: 09/01/07 (Java Web) Keywords: mysql, sql
Working with replication systems is not easy. Learning and testing different kind of MySQL replications and fine-tuning it to your specific needs is a time-consuming endeavor. Also you need to have several MySQL servers at your disposal. To overcome these limitations, Stardata created a MySQL 5 Replication Playground, a painless installation of 4 replicating nodes [...]
-
Tip: How To Find MySQL Version
Date: 09/01/07 (Java Web) Keywords: mysql, sql
Try: mysql -e status|grep ‘Server version’ If that doesn’t work then use: mysql -u root -p -e status|grep ‘Server version’ Provide root password when prompted. Default root password for MySQL is empty string, so just pressing enter when prompted for password will suffice by default. Windows users should simply run: mysql -e status or the equivalent with login specified and then look [...]
Source: http://blog.taragana.com/index.php/archive/tip-how-to-find-mysql-version/
-
MySQL Master-Master Replication…
Date: 09/02/07 (Java Web) Keywords: mysql, database, sql
Have you tried Master-Master replication (bi-directional slave-master replication) for MySQL? I am finding myself in a position where I need to have this replication to support the load on my blogs and sites. I would very much appreciate if you can share your experience & suggestions with master-master replication of MySQL database. I don’t want to [...]
Source: http://blog.taragana.com/index.php/archive/mysql-master-master-replication/
-
Two MySQL Questions.
Date: 09/05/07 (PHP Community) Keywords: mysql, sql
1.) I'm trying to build a Query where you have condition 1 which is absolute (like foo = 'Bar') but then condition 2 which could have several options. This is how it looks:
WHERE company_name = 'FOO' && entered_by = 'BOB' || entered_by = 'JOE' || entered_by = 'KAT'
What it is 'supposed' to do is pull up any records with company name of FOO that were entered by BOB, JOE, or KAT. But instead it's just pulling up all records entered regardless of the company_name restriction. What am I doing wrong here?
2.) Some of the company names use a & symbol which is wreaking havoc on the query. I've tried to use mysql_real_escape_string and addslashes but neither solve the problem; leaving a company name like R&F Cola reduced to a query search of R. How do I get MySQL to accept that the & is just part of the query?
Thank you so much to anyone who helps. I'm sure I"m missing something easy; but those are usually the most annoying ones! -
It's the simple things in life...
Date: 09/06/07 (PHP Community) Keywords: mysql, css, sql
Most of you are probably already doing this. But you never know.
Function to call to mark up table rows with CSS// odd_even // marks up table rows odd or even // returns string to echo function odd_even ($count) { if ($count & 1) { return ' class="odd" '; } else { return ' class="even" '; } }
And then...$count = 0; while ($row1 = mysql_fetch_array ($result1, MYSQL_ASSOC)) { $count++; echo '** table row data ** ';
...etc
Until I thought this little guy up I've been stupidly sticking the same routine in manually each time. It's the little things I often overlook. -
Basic SQL question
Date: 09/09/07 (MySQL Communtiy) Keywords: sql
The question from the beginner. Let's assume I have a table of students and classes they take. I know, how to retrieve number of classes for each student.
SELECT student COUNT(class) FROM tbl GROUP BY student;
I need to select all student who take 3 or more classes. What is the SQL syntaxis to say WHERE COUNT > 3?
Thank you. -
phpmyAdmin incosistency with the command line
Date: 09/10/07 (MySQL Communtiy) Keywords: php, mysql, sql
I am puzzled by the following issue. I run MySQL on my personal computer (Windows) and use phpMyAdmin to work with it. However, every attempt to use COUNT - produces the syntax error. phpMyAdmin doesn't recognize COUNT as a command. Moreover, every time I try to use tbl_name.field_name instead of just field.name I get the syntax error as well. However, if I copy exact the same SQL code (Copy/Paste) to DOS command line - everything seems to work great. How can it be at all? Does anybody have an idea? Thank you.
Windows XP, PHP 5.2.0, MySQL 5.0.27, PHPMyAdmin 2.9.1.1 Installed EasyPHP 1. 8 -
MySQL to get dynamic load balancing in 2008
Date: 09/13/07 (Open Source) Keywords: mysql, software, database, sql
MySQL will introduce dynamic load balancing to its database platform and enterprise subscription in 2008. That news surfaced during a briefing Thursday with MySQL executives about the open source database company’s forthcoming 5.1 and 6.0 upgrades. Zack Urlocker, Executive Vice President of Products at MySQL, said the load balancing software will ship as part of the next [...]
Source: http://feeds.feedburner.com/~r/zdnet/open-source/~3/156072251/
-
Wierd error
Date: 09/15/07 (Asp Dot Net) Keywords: database, sql, microsoft
I inherited a program that takes data from a csv file and inserts it into a database.
The data is in the following format:
8/20/2007 12:14:21 PM,HDFM,"Nick Yockell","Nate Wittrock",239-850-3931,239-850-2514,"Used 2004 Harley Davidson ","bought bike
CUSTOMER WANTS TO BRING HIS YAMAHA R6 IN TO TRADE AND SEE HOW MUCH PAYMENTS WOULD BE.
",Deliver,aa32ed31-f0c8-4f43-a7d4-830d9f29edcd
For some reason the function that saves the data to the database is having a problem with the customer phone number (5th column).
The database column is a varchar(50) - the same as the customer business phone number.
If the customer home phone contains anything other that digits it cannot be saved to the database.
However, the customer business phone can contain anything and gets saved just fine.
The sql statement in the second function runs fine from query analyzer so I know thats not the problem.
Is there anything in the two functions that could be causing this ?
private void ProcessFile(string filename, string vendor)
{
string path = string.Empty;
string LoggingDate = DateTime.Now.ToString();
switch (vendor.ToLower())
{
case "vpulse":
path = DIRECTORYPATHVPULSE;
break;
case "calllogpro":
path = DIRECTORYPATHCALLLOGPRO;
break;
}
try
{
OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+"\\"+";Extended Properties=\"Text;HDR=No;FMT=Delimited()\"");
//OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+this.txtSource.Text+"\\"+";Extended Properties=\"Text;HDR=No;\"");
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM "+filename,ExcelConnection);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelConnection.Open();
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
ExcelConnection.Close();
//Process File
ProcessFileRecords(ExcelDataSet, vendor.ToLower());
//Email Copy to Alliance Group
this.EmailFile(path, filename, vendor);
}
catch (Exception exc)
{
string errorText = exc.ToString();
System.Diagnostics.EventLog.WriteEntry("Alliance Level 2 DataRetreiver", errorText);
}
finally
{
//Archive Copy
this.ArchiveFile(path, filename);
}
}
private void ProcessFileRecordVPulse(object[] record)
{
string logdate = string.Empty;
string dealership = string.Empty;
string salesperson = string.Empty;
string customername = string.Empty;
string homephone = string.Empty;
string businessphone = string.Empty;
string product = string.Empty;
string comments = string.Empty;
string sellstage = string.Empty;
string vpulsecalltaskid = string.Empty;
string LoggingDate = DateTime.Now.ToString();
try
{
logdate = Convert.ToDateTime(record[0]).ToShortDateString();
dealership = record[1].ToString();
salesperson = record[2].ToString();
customername = record[3].ToString();
try
{
homephone = record[4].ToString();
//homephone.Replace("-", "");
}
catch
{
homephone = "Bad format";
}
businessphone = record[5].ToString();
product = record[6].ToString();
comments = record[7].ToString().Replace("
", " ");
//***Get Sales Stage
sellstage = record[8].ToString();
switch(sellstage.ToLower())
{
case "greet":
sellstage = "1";
break;
case "probe":
sellstage = "2";
break;
case "identify machine":
sellstage = "3";
break;
case "presentation":
sellstage = "4";
break;
case "sit down":
sellstage = "5";
break;
case "write up":
sellstage = "6";
break;
case "close the deal":
sellstage = "7";
break;
case "finance":
sellstage = "8";
break;
case "deliver":
sellstage = "9";
break;
default:
if (sellstage.ToLower().IndexOf("service") == -1 && sellstage.ToLower().IndexOf("parts") == -1)
sellstage = "1";
else
sellstage = sellstage;
break;
}
//***Get Sales Stage
vpulsecalltaskid = record[9].ToString();
//Import lead record
SqlCommand command = this.GetCommand();
command.CommandText = @"INSERT INTO CallLogs
(LogDate,
VPulseCallTaskId,
CallLogTypeId,
DealerId,
SalesPerson,
CustomerName,
CustomerHomePhone,
CustomerBusinessPhone,
Product,
Comments,
SaleStage,
LogProcessFlag,
CSI14Days,
CSI17Months,
CSI14DaysProcessFlag,
CSI17MonthsProcessFlag)
VALUES
(@LogDate,
@VPulseCallTaskId,
@CallLogTypeId,
@DealerId,
@SalesPerson,
@CustomerName,
@CustomerHomePhone,
@CustomerBusinessPhone,
@Product,
@Comments,
@SaleStage,
@LogProcessFlag,
@CSI14Days,
@CSI17Months,
@CSI14DaysProcessFlag,
@CSI17MonthsProcessFlag)";
command.Parameters.Add("@LogDate", logdate);
command.Parameters.Add("@VPulseCallTaskId", vpulsecalltaskid);
if ((sellstage.ToLower().IndexOf("service") == -1)&&(sellstage.ToLower().IndexOf("parts") == -1))
{
command.Parameters.Add("@CallLogTypeId", "1");
}
else
{
if (sellstage.ToLower().IndexOf("service") != -1)
{
command.Parameters.Add("@CallLogTypeId", "1"); //Service (4)
dealership = dealership+"_SERVICE";
sellstage = "1";
}
else if (sellstage.ToLower().IndexOf("parts") != -1)
{
command.Parameters.Add("@CallLogTypeId", "1"); //Parts (3)
dealership = dealership+"_PARTS";
sellstage = "1";
}
}
command.Parameters.Add("@DealerId", dealership);
command.Parameters.Add("@SalesPerson", salesperson);
command.Parameters.Add("@CustomerName", customername);
command.Parameters.Add("@CustomerHomePhone", homephone);
command.Parameters.Add("@CustomerBusinessPhone", businessphone);
command.Parameters.Add("@Product", product);
command.Parameters.Add("@Comments", comments);
command.Parameters.Add("@SaleStage", sellstage);
command.Parameters.Add("@LogProcessFlag", false);
command.Parameters.Add("@CSI14Days", Convert.ToDateTime(logdate).AddDays(14).ToString());
command.Parameters.Add("@CSI17Months", Convert.ToDateTime(logdate).AddMonths(17).ToString());
command.Parameters.Add("@CSI14DaysProcessFlag", false);
command.Parameters.Add("@CSI17MonthsProcessFlag", false);
command.ExecuteNonQuery();
this.DisposeCommand(command);
}
catch (Exception exc)
{
string errorText = exc.ToString();
System.Diagnostics.EventLog.WriteEntry("Alliance Level 2 DataRetreiver", errorText);
}Source: http://community.livejournal.com/aspdotnet/92096.html
-
max_user_connections problem
Date: 09/15/07 (MySQL Communtiy) Keywords: php, mysql, database, sql
I have a recurring problem with my site. It gets to max_user_connections very quickly. In
!['[info]']('http://stat.livejournal.com/img/community.gif')
php they advised me to make rapid-fire connections, and it works better than before, but it still builds up regularly. My host says that sometimes queries block the server. This is now a really serious problem and I'm in trouble with my host.
This happens since I tried to normalize the database by splitting information on other tables, instead of having fields with comma-separated lists. Thing is, now some query require several JOIN to get all the necessary information. Example:
$info_query = "SELECT $stories_table.id, $stories_table.series_id, $stories_table.story_num, $stories_table.story_title, $stories_table.pairing_extra, $stories_table.characters_extra, $stories_table.rating, $stories_table.spoilers, $stories_table.challenge, $stories_table.complete,
$series_table.user_id, $series_table.series_title,
$users_table.name,
$chapters_table.id as chid, CONCAT_WS('$chapter_separator', $chapters_table.chapter_num, $chapters_table.chapter_title) AS chapter_title, $chapters_table.chapter_num, $chapters_table.summary, $chapters_table.notes, DATE_FORMAT($chapters_table.date, '$date_format') AS date_f,
$ratings_table.name as rating_name
FROM $stories_table
LEFT JOIN $series_table ON $series_table.id=$stories_table.series_id
LEFT JOIN $users_table ON $users_table.id=$series_table.user_id
LEFT JOIN $chapters_table ON $stories_table.id = $chapters_table.story_id
LEFT JOIN $ratings_table ON $ratings_table.id = $stories_table.rating
WHERE $chapters_table.story_id = $story_id AND $chapters_table.chapter_num = $chapter_num";
Could this be the source of my problems? If so, do you know any alternative that doesn't load the server that much? I'm getting really desperate with this.
My host uses mysql version 4.0.27.
Thanks! -
Pattern match problem
Date: 09/16/07 (MySQL Communtiy) Keywords: mysql, sql
Hello. I am trying to learn how to solve a complicated (well, for me, I guess :)) ) problem in MySQL. But to do this I first try to figure out, how to solve a simpler problem. May somebody help me? I'll try to formulate it below
Let's say I have a table with student_name, quiz_date and grade (Pass/Fail). (Each student can have more than 1 quiz, not necessary the same number of quizes). I need to create a list of students, whose grades match the following pattern Pass/Fail/Pass in the adjacent(!) dates.
There are 2 main problems for me: 1) How to compare dates. 2) What is the right way to match patterns for a single student.
I would appreciate somebody giving me the decent way to solve this problem. Thank you a lot. -
Microsoft offers Oracle defectors up to 50 percent off SQL Server
Date: 09/19/07 (Data Management) Keywords: sql, microsoft
SQL Server 2008 isn't set to be released to manufacturing until the second quarter of next year. But Microsoft already is taking aim at Oracle with its forthcoming release. Microsoft officials announced on September 19 that they have no plans to increase the price of SQL Server...
-
Open tables question
Date: 09/24/07 (MySQL Communtiy) Keywords: mysql, sql
Following with my previous post (thanks to everyone who answered!) I've been looking around and I've seen there are a lot of unused open tables. mysql_stat says 244 open tables while SHOW OPEN TABLES lists 66. This seems a lot of tables. There is a way to close all these tables? Could it be part of the reason the max_user_connections is reached so quickly?
Also, my host says they updated my connections from 100 to 200. Is that much?
Many thanks in advance! -
Division used to work, it did...
Date: 09/27/07 (SQL Server) Keywords: sql
Here's a T-SQL (SQL2005) statement for you.
UPDATE @m_tblAllSalesStaging SET UnitsPercentChange4 = (TYUnits_4Wks/LYUnits_4Wks)*100 WHERE UnitsPercentChange4 IS NULL
Here's some of my result set (the columns are TYUnits_4Wks, LYUnits_4Wks, and UnitsPercentChange4):
5126 6240 0.00
24 156 0.00
64 141 0.00
10 36 0.00
2180 2541 0.00
0 2 0.00
2 48 0.00
0 12 0.00
More interesting, this is the same logic I'm using for the dollars columns that come just before, and the division is working swimmingly.
I have no clue why it's not picking anything up. Anyone have any ideas? Thanks.Source: http://community.livejournal.com/sqlserver/63508.html
-
Segfaultalicious
Date: 09/28/07 (PHP Community) Keywords: php, mysql, xml, sql, apache
I am just putting the finishing touches on a year long project that included in its design the use of an Op-Cache of some sort. After spending the last week and a half slamming a dev. server with siege for testing I noticed that occasionally the apache2 would seg. fault. We're using the LTS version of Ubuntu 6.04 with a stripped down environment dedicated and slaved to Apache w/memcache. From what I've researched and tested, this is actually a pretty well known issue with APC and also xcache. I've already written a simple daemon to watch the primary error log and if it spots a segfault fault error report to restart Apache, but this feels like using a band aid on a compound fracture.... Anyone have any other suggestions?
PHP compiled with just the bare minimums plus memcache, mysqli & mysql, JSON, and the XML library. -
Importing data
Date: 09/30/07 (MySQL Communtiy) Keywords: php, html, database, sql, web
Hi
I have an extremely large SQL dump file (541MB) that I am trying to run and drop the data into a database. I have attempted to use Big Dump which has worked for other databases in the past however I now run into the following error:Fatal error: Allowed memory size of 41943040 bytes exhausted (tried to allocate 16385 bytes) in /home/netyello/public_html/bigdump.php on line 514
What I'm wanting to do, if possible, is break down the dump file into smaller chunks so that it can handle it. Is anyone aware of a way to do this or a program that will allow me to do this when creating the dump file from the website?
Thanks! -
Zoho adds database to office suite
Date: 10/03/07 (Data Management) Keywords: mysql, database, sql, postgresql
Zoho is filling out it on demand suite with a database and reporting application. Zoho DB can convert spreadsheets into databases and supports pivot tables. It also supports SQL queries (Oracle, SQL Server, DB2, Sybase, MySQL, PostgreSQL, Informix and ANSI SQL dialects). ZohoDB has a rich...
-
check boxes on form
Date: 10/06/07 (PHP Community) Keywords: sql
Hello,
I have a form built for site preferences and check boxes with Yes / No questions.
How do I build a sql query and array to fill the check boxes with the users stored preferences? -
Importing a text file into MySQL
Date: 10/07/07 (Web Development) Keywords: mysql, database, sql
How can I insert this text file into a a MySQL database?
http://www.census.gov/tiger/tms/gazetteer/zips.txt"17","60661","IL","CHICAGO",87.642969,41.881351,2031,0.000178 "17","60666","IL","AMF OHARE",87.906803,41.9821,262,0.000023 "17","60901","IL","KANKAKEE",87.869607,41.116582,35952,0.003145 "17","60910","IL","AROMA PARK",87.771887,41.094653,3151,0.000276 "17","60911","IL","ASHKUM",87.941148,40.884431,1484,0.00013 "17","60912","IL","BEAVERVILLE",87.621715,40.967164,672,0.000059 "17","60913","IL","BONFIELD",88.061854,41.15731,1189,0.000104 "17","60914","IL","BOURBONNAIS",87.879023,41.166119,18311,0.001602 "17","60915","IL","BRADLEY",87.860115,41.145376,10071,0.000881
-
image galleries
Date: 10/16/07 (PHP Community) Keywords: mysql, database, sql, web
hi all!
I'm in need of an image gallery script, and was wondering if anyone had any suggestions? I've been browsing around and testing a few and can't quite find one that matches what i need.
i need to be able to upload the photos via the website, for thumbnails to be created and then clicking on the thumbnail takes you to the full size image.
However, all the scripts i've found, when making the thumbnails, make them square, where as i'd prefer it to be more dynamic. So say each thumbnail had a height of 90px, and the width was determined from the full size image, rather than being fixed to 90px aswell. That way when displayed on the page, tall images don't look squashed.
Any scripts out there like that? I don't mind if it is linked to a mysql database or not, tho most don't seem to be.
Ideally it would be seperated into admin area and display area, so that i can customize the look of the displayed photo pages.
| Previous page | || | Next page |