Search Results

Search found 96031 results on 3842 pages for 'mysql server'.

Page 89/3842 | < Previous Page | 85 86 87 88 89 90 91 92 93 94 95 96  | Next Page >

• SQL Server: Must numbers all be specified with latin numeral digits?

  - by Ian Boyd

  Does SQL server expect numbers to be specified with digits from the latin alphabet, e.g.: 0123456789 Is it valid to give SQL Server digits in other alphabets? Rosetta Stone: Latin: 01234567890 Arabic: ?????????? Bengali: ?????????? i know that the client (ADO) will convert 8-bit strings to 16-bit unicode strings using the current culture. But the client is also converting numbers to strings using their current culture, e.g.: SELECT * FROM Inventory WHERE Quantity > ???,?? Which throws SQL Server for fits. i know that the server/database has it's defined code page and locale, but that is for strings. Will SQL Server interpret numbers using the active (or per-login specified) locale, or must all numeric values be specifid with latin numeral digits?

  Read the article

• Windows Server 2003 RDP not listening on IPv6

  - by Ian Boyd

  i have a Windows Server 2003 machine; with IPv6 enabled: Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : newland.local IP Address. . . . . . . . . . . . : 192.168.1.244 Subnet Mask . . . . . . . . . . . : 255.255.0.0 IP Address. . . . . . . . . . . . : 2001:470:¦¦¦¦:¦¦¦¦:¦¦¦:¦¦¦¦:¦¦¦¦:¦¦¦¦ IP Address. . . . . . . . . . . . : fe80::224:1dff:fe86:fdf2%4 Default Gateway . . . . . . . . . : 192.168.1.1 fe80::250:bfff:fe91:955f%4 i can connect to IIS server using IPv, remotely and locally, using IPv4 and IPv6: > telnet 127.0.0.1 80 (connects) > telnet 192.168.1.244 80 (connects) > telnet ::1 80 (connects) > telnet fe80::224:1dff:fe86:fdf2 80 (connects) And TCPView shows that the server is listening on port 80: Note: This is useful to establish that Windows Server 2003 does have support for IPv6 services. And i can connect to terminal services, locally and remotely, using IPv4: > telnet 127.0.0.1 3389 (connects) > telnet 192.168.1.244 3389 (connects) But i cannot connect to RDP, locally or remotely, over IPv6: > telnet ::1 3389 (fails to connect) > telnet fe80::224:1dff:fe86:fdf2 3389 (fails to connect) We can see that the system is listening on 3389: Except why won't it listen on port 3389 (ipv6)? Unfortunately it's not the firewall. Aside from the fact that i'm connecting locally (in which case the firewall doesn't apply), the firewall doesn't apply:

  Read the article

• SQL SERVER – Weekly Series – Memory Lane – #048

  - by Pinal Dave

  Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane. 2007 Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used Above theory is true in most of the cases. However SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest.In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index. Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT One of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them. 2008 SQL SERVER – Guidelines and Coding Standards Complete List Download Coding standards and guidelines are very important for any developer on the path of a successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding. Download Guidelines and Coding Standards complete List Download Get Answer in Float When Dividing of Two Integer Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected. Puzzle – Computed Columns Datatype Explanation SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast. Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures I have written many articles about renaming a tables, columns and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. The interesting fact is that when we rename a stored procedure using SP_Rename command, the Stored Procedure is successfully renamed. But when we try to test the procedure using sp_helptext, the procedure will be having the old name instead of new names. 2009 Insert Values of Stored Procedure in Table – Use Table Valued Function It is clear from the result set that , where I have converted stored procedure logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. The performance of the stored procedure is “usually” better than that of functions. Interesting Observation – Index on Index View Used in Similar Query Recently, I was working on an optimization project for one of the largest organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.” Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries Working with SQL Server has never seemed to be monotonous – no matter how long one has worked with it. Quite often, I come across some excellent comments that I feel like acknowledging them as blog posts. Recently, I wrote an article on SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location, which is well received in the community. 2010 I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post. SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1 SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2 SQL SERVER – Index Created on View not Used Often – Limitation of the View 3 SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4 SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5 SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6 SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7 SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8 SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9 SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10 SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11 2011 Startup Parameters Easy to Configure If you are a regular reader of this blog, you must be aware that I have written about SQL Server Denali recently. Here is the quickest way to reach into the screen where we can change the startup parameters. Go to SQL Server Configuration Manager >> SQL Server Services >> Right Click on the Server >> Properties >> Startup Parameters 2012 Validating Unique Columnname Across Whole Database I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples. For example “Our business requirement is when we add new column we want it unique across current database.” Read the solution to this strange request in this blog post. Excel Losing Decimal Values When Value Pasted from SSMS ResultSet It is very common when users are coping the resultset to Excel, the floating point or decimals are missed. The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Basic Calculation and PEMDAS Order of Operation Read this interesting blog post for fantastic conversation about the subject. Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video http://www.youtube.com/watch?v=x_-3tLqTRv0 Delete From Multiple Table – Update Multiple Table in Single Statement There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them. Let us see the questions here. I want to delete from multiple table in a single statement how will I do it? I want to update multiple table in a single statement how will I do it? Read the answer in the blog post. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Big Data – ClustrixDB – Extreme Scale SQL Database with Real-time Analytics, Releases Software Download – NewSQL

  - by Pinal Dave

  There are so many things to learn and there is so little time we all have. As we have little time we need to be selective to learn whatever we learn. I believe I know quite a lot of things in SQL but I still do not know what is around SQL. I have started to learn about NewSQL recently. If you wonder what is NewSQL I encourage all of you to read my blog post about NewSQL over here Big Data – Buzz Words: What is NewSQL – Day 10 of 21. NewSQL databases are quickly becoming popular – providing the scale of NoSQL with the SQL features and transactions. As a part of learning NewSQL database, I have recently started to learn about ClustrixDB. ClustrixDB has been the most mature NewSQL database used by some of the largest internet sites in the world for over 3 years, with extensive SQL support. In addition to scale, it provides fast real-time analytics by bringing massively parallel processing (MPP), available only in warehousing databases, to the transactional database. The reason I am more intrigued about learning ClustrixDB is their recent announcement on Oct 31. ClustrixDB was only available as an appliance, but now with their software release on Oct 31, everyone can use it. It is now available as forever free for up to 12 cores with community support, and there is a 45 day trial for unlimited cluster sizes. With the forever free world, I am indeed interested in ClustrixDB now. I know that few of the leading eCommerce sites in the world uses them for their transactional database. Here are few of the details I have quickly noted for ClustrixDB. ClustrixDB allows user to: Scale by simply adding nodes to the cluster with a single command Run billions of transactions a day Run fast real-time analytics Achieve high-availability with recovery from node failure Manages itself Easily migrate from MySQL as it is nearly plug-and-play compatible, use MySQL drivers, tools and replication. While I was going through the documentation I realized that ClustrixDB also has extensive support for SQL features including complex queries involving joins on a dozen or more tables, aggregates, sorts, sub-queries. It also supports stored procedures, triggers, foreign keys, partitioned and temporary tables, and fully online schema changes. It is indeed a very matured product and SQL solution. Indeed Clusterix sound very promising solution, I decided to dig a bit deeper to understand who are current customers of the Clustrix as they exist in the industry for quite a few years. Their client list is indeed very interesting and here is my quick research about them. Twoo.com – Europe’s largest social discovery (dating) site runs 4.4 Billion Transactions a day with table sizes over a Terabyte, on a 168 core cluster. EngageBDR – Top 3 in the online advertising category uses ClustrixDB to serve 6.9 billion ads a day through real-time bidding platform. Their reports went from 4 hours to 15 seconds. NoMoreRack – Top 2 fastest growing e-commerce company in US used ClustrixDB for high availability and fast growth through Amazon cloud. MakeMyTrip – India’s leading travel site runs on ClustrixDB with two clusters running as multi-master in Chennai and Bangalore. Many enterprises such as AOL, CSC, Rakuten, Symantec use ClustrixDB when their applications need scale. I must accept that I am impressed with the information I have learned so far and now is the time to do some hand’s on experience with their product. I want to learn this technology so in future when it is about NewSQL, I know what I am talking about. Read more why Clustrix explains why you ClustrixDB might be the right database for you. Download ClustrixDB with me today and install it on your machine so in future when we discuss the technical aspects of it, we all are on the same page. The software can be downloaded here. Reference : Pinal Dave (http://blog.SQLAuthority.com)Filed under: Big Data, MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: Clustrix

  Read the article

• Alter charset and collation in all columns in all tables in MySQL

  - by The Disintegrator

  I need to execute these statements in all tables for all columns. alter table table_name charset=utf8; alter table table_name alter column column_name charset=utf8; Is it possible to automate this in any way inside MySQL? I would prefer to avoid mysqldump Update: Richard Bronosky showed me the way :-) The query I needed to execute in every table: alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Crazy query to generate all other queries: SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname'; I only wanted to execute it in one database. It was taking too long to execute all in one pass. It turned out that it was generating one query per field per table. And only one query per table was necessary (distinct to the rescue). Getting the output on a file was how I realized it. How to generate the output to a file: mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql And finally to execute all the queries: mysql --user=user --password=secret < alter.sql Thanks Richard. You're the man!

  Read the article

• python mysqldb - mysql server gone away - can't reconnect

  - by david.barkhuizen

  Hi Folks, When attempting to import a bunch of data into mysql tables using python and mysqldb, I run into the following error '2006 - mySQL Server has gone away', and then I am unable to reconnect again within the script. I am iniitially re-using a connection object across transactions ( delineated by conn.commit() ), then when I first encounter this exception, if I create a new connection by calling MySQLdb.connect(), this new connection also fails with the same exception. This error does not occur immediately, I can pump a fair amount of data into the db, but then faithfully occurs after I have inserted a couple thousand records, so roughly once the db has committed a certain transaction volume, it always falls over like this. If I rerun the script, WITHOUT restarting the db server. then it resumes where it left off, pumps in some data, then falls over again. Before recommendations to change time-out timings, does anyone know why I am not able to establish a new connection after the initial failure ? - Even if I try a couple of times waiting a couple of seconds between each. (btw, I'm running Windows 7, mysql server 5.1.48, mysqldb 1.2.3.gamma.1, python 2.6)

  Read the article

• High mysql server load, sar output

  - by eric

  I have a MySQL Server that should be performing better than it seems to be. We're running ubuntu on a Amazon Cluster Compute (cc1.4xlarge) Linux ip-10-0-1-60 3.2.0-25-virtual #40-Ubuntu SMP Wed May 23 22:20:17 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Distributor ID: Ubuntu Description: Ubuntu 12.04 LTS Release: 12.04 Codename: precise I have several output files from sar that i'm not really sure how to interpret. For example, I ran: # Individual block device I/O activities sar -d 1 180 > logs/block_device_io.log & which gave me what looks like really high utilisation of my disk (turns out this block device maps to /dev/xvdh on /var/lib/mysql type ext4 (rw,_netdev) The output from my log: 10:48:59 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 10:49:00 PM dev202-16 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10:49:00 PM dev202-32 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10:49:00 PM dev8-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10:49:00 PM dev202-112 1008.00 31040.00 1416.00 32.20 1.02 1.01 0.89 90.00 10:49:00 PM dev202-80 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Am I wrong in thinking this is a problem? I have it above 90% almost the entire time we're seeing slowness. Or does this just mean MySQL is doing what it's supposed to do?

  Read the article

• Control Panel for MySQL and PostgreSQL server

  - by jfreak53

  I am looking for a control panel for a CentOS system that will allow me to add user's that can control their "own" MySQL and PostgreSQL Databases. I don't want to have to spend the $25 a month for cpanel on a dedicated to do this. Plus cPanel comes with all the rest like webserver and email that I don't need these to have. Basically I want to be able to create users that can create their own databases and only see those that they have created. I want to be able to control their disk space as with most panels. They need to be able to create their own DB users as well. Kloxo won't work as it doesn't natively support PostgreSQL. I tried straight PHPMyAdmin, but it won't let the user's create their own DB's unless they can also see everyone else's. VirtualMin I just can't get to work at all! ha ha I installed it an though it works great for itself if a user signs into Usermin they can see all DB's. If they sign into PHPMyAdmin (which basically means any program that directly connects to MySQL) they see all DB's. If they login to virtualmin then yes, they only see their's. But that won't work. I can't seem to think of another way to do this. I can use webmin and usermin directly but PHPMyAdmin again let's the user's I create either see only one DB and create none, or see all DB's. So that sound's like a permission problem in MySQL and PostgreSQL.

  Read the article

• Intermittent "Lost connection to MySQL server at 'reading initial communication packet'"

  - by db2

  Our web environment consists of two servers. Web front-end. Dell PowerEdge R610, RHEL 5.5, Apache 2.2.17, php 5.2.14. Database server. Dell PowerEdge R710, Windows 2008 R2 Standard x64, MySQL 5.5.11-log x64. Normally these two work perfectly fine together. However, when I try to get them talking via a dedicated LAN on their secondary NICs (each machine has four of them), things get flaky. I have NIC #2 on both machines configured on the 172.16.1.0/24 subnet, with no gateway or DNS servers (obviously, since it's just those two systems), and I put the private IP address of each machine into the hosts file of the other. The routing tables on both machines look okay after I do this. I've tried this with both a crossover cable draped directly between the two NICs, and also via a dedicated vlan on the switch in the rack. In either case, I get intermittent connection problems. It's a fairly small percentage of connections that fail, but it's enough to cause a significant problem, and I have to switch back to the main network connection, which will contend with all the other traffic and hosts on the switch. The full error message that appears in the application log: SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 110 Am I doing something really dumb that's causing this to not work properly? Anything I can check in MySQL that would explain why it's failing to connect occasionally?

  Read the article

• bind9 dlz/mysql at ubuntu segfault libmysqlclient.so

  - by Theos

  I have a big problem. I installed the bind9 nameserver to three different computer. two Ubuntu 10.04.4 LTS, and one Ubuntu 11.10 I compiled it 9.7.0, 9.7.3, 9.9.0 with this method: ./configure --prefix=/usr --sysconfdir=/etc/bind --localstatedir=/var \ --mandir=/usr/share/man --infodir=/usr/share/info \ --enable-threads --enable-largefile --with-libtool --enable-shared --enable-static \ --with-openssl=/usr --with-gssapi=/usr --with-gnu-ld \ --with-dlz-mysql=yes --with-dlz-bdb=no \ --with-dlz-filesystem=yes --with-geoip=/usr make make install After the set up for dlz/mysql, the BIND server is working perfetctly until 5-30 minute long. Ahter i got segfault. I resolve temporaly the problem with a simple process watchdog, and if the named is stopped, the watchdog is restart it, but this is not a good idea in long therm. My log output is: messages: Apr 13 19:33:51 dnsvm kernel: [ 8.088696] eth0: link up Apr 13 19:33:58 WATCHDOG: named not running. Restarting Apr 13 19:35:08 dnsvm kernel: [ 87.082572] named[1027]: segfault at 88 ip b71c4291 sp b5adfe30 error 4 in libmysqlclient.so.16.0.0[b714e000+1aa000] Apr 13 19:35:08 WATCHDOG: named not running. Restarting Apr 13 19:35:08 dnsvm kernel: [ 87.457510] named[1423]: segfault at 68 ip b71d6122 sp b52f0a40 error 4 in libmysqlclient.so.16.0.0[b7160000+1aa000] Apr 13 19:35:09 WATCHDOG: named not running. Restarting Apr 13 19:41:56 dnsvm kernel: [ 494.838206] named[1448]: segfault at 88 ip b731c291 sp b5436e30 error 4 in libmysqlclient.so.16.0.0[b72a6000+1aa000] Apr 13 19:41:57 WATCHDOG: named not running. Restarting Apr 13 19:57:26 dnsvm kernel: [ 1424.023409] named[2976]: segfault at 88 ip b72d1291 sp b6beee30 error 4 in libmysqlclient.so.16.0.0[b725b000+1aa000] Apr 13 19:57:26 WATCHDOG: named not running. Restarting Apr 13 20:11:56 dnsvm kernel: [ 2294.324663] named[6441]: segfault at 88 ip b7357291 sp b6473e30 error 4 in libmysqlclient.so.16.0.0[b72e1000+1aa000] Apr 13 20:11:57 WATCHDOG: named not running. Restarting syslog: http://pastebin.com/hjUyt8gN the first server is a native, normal x64 server (u1004lts), the second is virtualised server (u11.10) the third is also virtualised (10.04lts) This servers is only for dns providing with mysql server db. But the problem is be with all server, and all bind version. named.conf: http://pastebin.com/zwm1yP7V Can anybody help me, or any good idea?

  Read the article

• Mysql can not resolve hostnames when checking privileges

  - by Fabio

  I'm going crazy to solve this. I have a mysql installation (on machine db.example.org) which doesn't resolve a given hostname. I gave privileges using hostnames i.e. GRANT USAGE ON *.* TO 'user'@'host1.example.org' IDENTIFIED BY PASSWORD 'secret' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON `my_database`.* TO 'user'@'host1.example.org' However when I try to connect using mysql -u user -p -h db.example.org I obtain ERROR 1045 (28000): Access denied for user 'user'@'192.168.11.244' (using password: YES) I already checked for correct name resolution in the dns system: $ dig -x 192.168.11.244 ;; ANSWER SECTION: 244.11.168.192.in-addr.arpa. 68900 IN PTR host1.example.org. I've also checked for skip-name-resolve option in mysql variables in fact if I can access from another machine on the same subnet using hostname privileges. The only difference is that host1.example.org and db.example.org point the same ip on the same machine i.e. both db.example.org and host1.example.org have ip 192.168.11.244. In this way all the applications using that database can use the name db.example.org and we can move the data on other hosts (if needed) just by changing the dns record, leaving the application code unchanged. What should I do to solve this or at least to understand what's happening?

  Read the article

• MySQL Server Is Slow

  - by user2853965746

  I have two MySQL servers and one was just recently setup. The one I just recently setup is a bit slower than my older one, which kind of bothers me because I don't want my clients to be upset with the speed difference when I launch the new one. The older server runs on Ubuntu (~13.04 I believe) and the new one is on Debian 6. Both servers are 2GB ram, but my newer server is has an SSD, so I thought it might be the same speed if not faster. Anyway, the speed difference isn't too much (both are still under a second, but still noticeable). Whenever I select 50 rows from the user table on my older server (SELECT * FROM users LIMIT 50), I get the results in 0.003 s. There is 100,000+ accounts in that table. Whenever running the same command on the same table with only six dev accounts, it takes 0.069 s. It may not seem like a lot, but it's noticeable when you're used to a fast response. I added skip-name-resolve to the config and it didn't seem to help. Basically I'm asking if anyone knows what can cause a MySQL server to be slow in Debian 6? Should I just drop it and switch to Ubuntu like the older server (I don't think the OS is the problem, but you never know)? The older server is under a lot of use too, it's used a lot for web api's on my website. A lot of connections and stuff, and it still remains fast.

  Read the article

• MYSQL - Multiple set values in one update statement [migrated]

  - by Maurzank

  MYSQL - MULTIPLE SET VALUES IN ONE UPDATE STATEMENT USING 2 TABLES AS REFERENCE AND STORING VALUES IN ONE OF THOSE TABLES WITH A SPECIFIC LOGIC. Hello people, A problem came up by making an UPDATE. The example issue is as follows: CURRENUSRTABLE +------------+-------+ | ID | STATE | +------------+-------+ | 123 | 3 | | 456 | 3 | | 789 | 3 | +------------+-------+ HISTORYTABLE +------------+------------+-----+ | ID | TRDATE | ACT | +------------+------------+-----+ | 123 | 2013-11-01 | 5 | | 456 | 2013-11-01 | 5 | | 789 | 2013-11-01 | 5 | | 123 | 2013-11-02 | 4 | | 456 | 2013-11-02 | 4 | | 789 | 2013-11-02 | 4 | | 123 | 2013-11-03 | 3 | | 456 | 2013-11-03 | 3 | | 789 | 2013-11-03 | 3 | +------------+------------+-----+ I'm using these variables: @BA=3, @DE=5, @BL=4, What I'm trying to do is an update on CURRENUSRTABLE.STATE using HISTORYTABLE.ACT with the following logic: STATE value will be updated as ACT value, except when STATE value is 4 and ACT is 3, then STATE will be 5 I made this statement: UPDATE CURRENUSRTABLE RIGHT OUTER JOIN HISTORYTABLE ON HISTORYTABLE.ID=CURRENUSRTABLE.ID SET CURRENUSRTABLE.STATE= ( SELECT CASE HISTORYTABLE.ACT WHEN @DE THEN @DE WHEN @BL THEN @BL WHEN @BA THEN CASE CURRENUSRTABLE.STATE WHEN @BL THEN @DE ELSE @BA END END ORDER BY HISTORYTABLE.TRDATE,FIELD(HISTORYTABLE.ACT,@DE,@BL,@BA) ) WHERE HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-01' I'm intentionally using "RIGHT OUTER JOIN" and "HISTORYTABLE.TRDATE BETWEEN" because I'd like to change the values in CURRENUSRTABLE using a timeframe of more than one day. If I execute this statement many times using only one day (i.e. "BETWEEN '2013-11-01' AND '2013-11-01'" and then "BETWEEN '2013-11-02' AND '2013-11-02'"... etc ) it works perfectly, but if it is executed using the dates "BETWEEN '2013-11-01' AND '2013-11-03'" the results on CURRENUSRTABLE.STATE are 3, which is wrong, it should be 5. I think the problem relies on "CASE CURRENUSRTABLE.STATE" when uses "HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-03'", because it reads the STATE 9 times which has not been commited yet until the statement ends. Query OK, 9 rows affected (0.00 sec) Rows matched: 9 Changed: 9 Warnings: 0 Maybe the solution is very simple, but unfortunately I've not much practice on MySQL since I've worked with it less than 2 months :) Is there any suggestions to solve this issue? PD: MySQL version is 4.1.22, I know is very old an EOL, unfortunately I have to make these statements on this version. Thanks!

  Read the article

• CSV engine on MySQL server

  - by Jeff

  I don't think that this is a programming question so I am going to ask it here - Reading the book high performance mysql, I read about the CSV engine. The paragraph says: The CSV engine can treat comma-separated values (CSV) files as table, but it does not support indexes on them. This engine lets you copy files in and out of the database while the server is running. If you export a CSV file from a spreadsheet and save it in the MySQL server's data directory, the server can read it immediately. Similary, if you write data to a CSV table, an external program can read it right away. CSV tables are especially useful as a data interchange format and for certain kinds of logging. What I get from this paragraph is that I can copy a .CSV file into the data directory of database, and it should show as a table that is able to be read from. However, whenever I copy a test .csv file into the directory, it does not appear as a table. I can't access it. I am using MySQL 5.5 also Does anyone know why this is not working, or what I am doing wrong? Thanks

  Read the article

• apache2 mysql authentication module and SHA1 encryption

  - by Luca Rossi

  I found myself in a setup on where I need to enable some authentication method using mysql. I already have an user scheme. That user scheme is working like a charm with MD5 password and CRYPT, but when I turn to SHA1sum it says: [Fri Oct 26 00:03:20 2012] [error] Unsupported encryption type: Sha1sum No useful debug informations on log files. This is my setup and some info: debian6 apache and ssl installed packages: root@sistemichiocciola:/etc/apache2/mods-available# dpkg --list | grep apache ii apache2 2.2.16-6+squeeze8 Apache HTTP Server metapackage ii apache2-mpm-prefork 2.2.16-6+squeeze8 Apache HTTP Server - traditional non-threaded model ii apache2-utils 2.2.16-6+squeeze8 utility programs for webservers ii apache2.2-bin 2.2.16-6+squeeze8 Apache HTTP Server common binary files ii apache2.2-common 2.2.16-6+squeeze8 Apache HTTP Server common files ii libapache2-mod-auth-mysql 4.3.9-13+b1 Apache 2 module for MySQL authentication ii libapache2-mod-php5 5.3.3-7+squeeze14 server-side, HTML-embedded scripting language (Apache 2 module) root@sistemichiocciola:/etc/apache2/sites-enabled# dpkg --list | grep ssl ii libssl-dev 0.9.8o-4squeeze13 SSL development libraries, header files and documentation ii libssl0.9.8 0.9.8o-4squeeze13 SSL shared libraries ii openssl 0.9.8o-4squeeze13 Secure Socket Layer (SSL) binary and related cryptographic tools ii openssl-blacklist 0.5-2 list of blacklisted OpenSSL RSA keys ii ssl-cert 1.0.28 simple debconf wrapper for OpenSSL my vhost setup: AuthMySQL On Auth_MySQL_Host localhost Auth_MySQL_User XXX Auth_MySQL_Password YYY Auth_MySQL_DB users AuthName "Sistemi Chiocciola Sezione Informatica" AuthType Basic # require valid-user require group informatica Auth_MySQL_Encryption_Types Crypt Sha1sum AuthBasicAuthoritative Off AuthUserFile /dev/null Auth_MySQL_Password_Table users Auth_MYSQL_username_field email Auth_MYSQL_password_field password AuthMySQL_Empty_Passwords Off AuthMySQL_Group_Table http_groups Auth_MySQL_Group_Field user_group Have I missed a package/configuration or something?

  Read the article

• Architecture for highly available MySQL with automatic failover in physically diverse locations

  - by Warner

  I have been researching high availability (HA) solutions for MySQL between data centers. For servers located in the same physical environment, I have preferred dual master with heartbeat (floating VIP) using an active passive approach. The heartbeat is over both a serial connection as well as an ethernet connection. Ultimately, my goal is to maintain this same level of availability but between data centers. I want to dynamically failover between both data centers without manual intervention and still maintain data integrity. There would be BGP on top. Web clusters in both locations, which would have the potential to route to the databases between both sides. If the Internet connection went down on site 1, clients would route through site 2, to the Web cluster, and then to the database in site 1 if the link between both sites is still up. With this scenario, due to the lack of physical link (serial) there is a more likely chance of split brain. If the WAN went down between both sites, the VIP would end up on both sites, where a variety of unpleasant scenarios could introduce desync. Another potential issue I see is difficulty scaling this infrastructure to a third data center in the future. The network layer is not a focus. The architecture is flexible at this stage. Again, my focus is a solution for maintaining data integrity as well as automatic failover with the MySQL databases. I would likely design the rest around this. Can you recommend a proven solution for MySQL HA between two physically diverse sites? Thank you for taking the time to read this. I look forward to reading your recommendations.

  Read the article

• Error code 1005 (errno: 121) upon create table while restoring MySQL database from a dump

  - by Jonathan

  I have a linux prod machine and a Win7 64bit dev machine. My workflow includes dumping the production MySQL database on the linux machine and restoring it in my local MySQL database on the windows machine (using SQLyog). This worked fine for a long time. Following some trouble, I formatted and reinstalled my windows dev machine. Since then I'm unable to restore the db on it. I keep receiving the following error: Query: CREATE TABLE `auth_group` ( `id` int(11) NOT NULL auto_increment, `name` varchar(80) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Error occured at:2010-06-26 17:16:14 Line no.:30 Error Code: 1005 - Can't create table 'ap_site.auth_group' (errno: 121) Notice that this is the first create table statement in the sql dump file. This error occurs both on MySQL Community Server 5.1.41 and 5.1.48 and with SQLyog Community 8.0.4 and 8.5.1. I really don't know what's different in my configuration from before the reinstall and now and why does it have this effect. Restoring from sql dump is something I need to keep on doing, so I need a permanent fix and not a tailored workaround.

  Read the article

• Dovecot unable to perform mysql query

  - by NathanJ2012

  I have been following the ISPMail tutorials on workaround.org (the 2.9 Wheezy version) and thus far everything has been working fine. When I reached the step to "Testing email delivery" step I noticed a error about the query in the output log from /var/log/mail.log. May 14 06:48:59 mail postfix/pickup[17704]: EA4AD240A98: uid=0 from=<root> May 14 06:48:59 mail postfix/cleanup[17776]: EA4AD240A98: message-id=<[email protected]> May 14 06:48:59 mail postfix/qmgr[17706]: EA4AD240A98: from=<[email protected]>, size=429, nrcpt=1 (queue active) May 14 06:49:00 mail dovecot: auth-worker(17782): mysql(127.0.0.1): Connected to database mailserver May 14 06:49:00 mail dovecot: auth-worker(17782): Warning: mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist May 14 06:49:00 mail dovecot: auth-worker(17782): Error: sql([email protected]): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') May 14 06:49:00 mail dovecot: lda([email protected]): msgid=<[email protected]>: saved mail to INBOX May 14 06:49:00 mail postfix/pipe[17780]: EA4AD240A98: to=<[email protected]>, relay=dovecot, delay=0.09, delays=0.03/0.01/0/0.06, dsn=2.0.0, status=sent (delivered via dovecot service) May 14 06:49:00 mail postfix/qmgr[17706]: EA4AD240A98: removed I found this rather interesting that it isn't finding the DB so I went back through and checked EVERY file that I touched that involved the DB (including the postfix cf files) and everything is correct so I am baffled at this point, but oddly enough it would seem the email still made it to the correct destination in /var/vmail/domain.com/. Should I be worried about this or am I missing something here? Since it is a message from dovecot it would be the query from dovecot-sql.conf.ext which I am including here driver = mysql connect = host=127.0.0.1 dbname=mailserver user=blocked password=***REMOVED*** default_pass_scheme = PLAIN-MD5 password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

  Read the article

• What are the possible disadvantages of enabling the "data access" server option in sys.servers for t

  - by Corp. Hicks

  We plan to change the default server options of an SQL2k5 server instance by enabling data access. The reason is that we want to run "SELECT * FROM OPENQUERY(LOCALSERVER, '...')" -like statements on the server. What are the possible disadvantages of enabling server option "data access" (alias sys.servers.is_data_access_enabled) for the local server (sys.servers.server_id = 0)? (There must be a reason for MS setting this option to disabled by default...) EDIT: it turns out that I'm not the first person to ask this question: http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx "The DATA ACCESS server option is not very well documented in my opinion - the Books On Line say it is a property of linked servers. It doesn't mention at all that you actually can have it enabled on your local server to enable OPENQUERY calls. I noticed that when you disable DATA ACCESS on a linked server, you can't query any table located on it (I tested it on my loopback server) neither using OPENQUERY nor four-part naming convention. You can still call procedures (with four-part naming) that return rowsets. Well, the interesting question is why it is disabled by default on local server - I suppose to discourage users from using OPENQUERY against it." It also seems that the author of the post (Pjotr Rodak) is a Stack Overflow user :-)

  Read the article

• Does MySQL log successful or attempted queries?

  - by Nathan Long

  I'm trying to track down a hit-or-miss bug in a web application. Sometimes a request completes just fine; sometimes it hangs and never finishes. I see that Apache now has several requests listed on the server-status page as "sending reply," and that doesn't change. I'm testing on localhost, so there shouldn't ever be more than one. Out of curiosity, I set MySQL to log all queries and I'm tail -fing the log file. When things go OK, I see a pattern like this: 20 Connect root@localhost on dbname 20 Query (some query #1) 20 Query (some query #2) (etc) 20 Quit 21 Connect (etc) When it hangs, I see a pattern like this: 22 Connect root@localhost on dbname 22 Query (some query #1) //nothing happens, so I try the post again 23 Connect root@localhost on dbname 23 Query (some query #1) //nothing happens; try again 24 Connect (etc) Here's my question: is MySQL logging attempted queries, or successful queries? In other words, if the last line I see is query #1, does that imply that query #1 or query #2 is hanging? My guess is that the one I don't see is the problem, because the last one I see looks fine, but maybe the one I don't see is too screwed-up for MySQL to process. Thoughts?

  Read the article

• HP Proliant DL380 G4 - Can this server still perform in 2011?

  - by BSchriver

  Can the HP Proliant DL380 G4 series server still perform at high a quality in the 2011 IT world? This may sound like a weird question but we are a very small company whose primary business is NOT IT related. So my IT dollars have to stretch a long way. I am in need of a good web and database server. The load and demand for a while will be fairly low so I am not looking nor do I have the money to buy a brand new HP Dl380 G7 series box for $6K. While searching around today I found a company in ATL that buys servers off business leases and then stripes them down to parts. They clean, check and test each part and then custom "rebuild" the server based on whatever specs you request. The interesting thing is they also provide a 3-year warranty on all their servers they sell. I am contemplating buying two of the following: HP Proliant DL380 G4 Dual (2) Intel Xeon 3.6 GHz 800Mhz 1MB Cache processors 8GB PC3200R ECC Memory 6 x 73GB U320 15K rpm SCSI drives Smart Array 6i Card Dual Power Supplies Plus the usual cdrom, dual nic, etc... All this for $750 each or $1500 for two pretty nicely equipped servers. The price then jumps up on the next model up which is the G5 series. It goes from $750 to like $2000 for a comparable server. I just do not have $4000 to buy two servers right now. So back to my original question, if I load Windows 2008 R2 Server and IIS 7 on one of the machines and Windows 2008 R2 server and MS SQL 2008 R2 Server on another machine, what kind of performance might I expect to see from these machines? The facts is this series is now 3 versions behind the G7's and this series of server was built when Windows 200 Server was the dominant OS and Windows 2003 Server was just coming out. If you are running Windows 2008 R2 Server on a G4 with similar or less specs I would love to hear what your performance is like.

  Read the article

• MySQL is killing the server IO.

  - by OneOfOne

  I manage a fairly large/busy vBulletin forums (running on gigenet cloud), the database is ~ 10 GB (~9 milion posts, ~60 queries per second), lately MySQL have been grinding the disk like there's no tomorrow according to iotop and slowing the site. The last idea I can think of is using replication, but I'm not sure how much that would help and worried about database sync. I'm out of ideas, any tips on how to improve the situation would be highly appreciated. Specs : Debian Lenny 64bit ~12Ghz (6 cores) CPU, 7520gb RAM, 160gb disk. Kernel : 2.6.32-4-amd64 mysqld Ver 5.1.54-0.dotdeb.0 for debian-linux-gnu on x86_64 ((Debian)) Other software: vBulletin 3.8.4 memcached 1.2.2 PHP 5.3.5-0.dotdeb.0 (fpm-fcgi) (built: Jan 7 2011 00:07:27) lighttpd/1.4.28 (ssl) - a light and fast webserver PHP and vBulletin are configured to use memcached. MySQL Settings : [mysqld] key_buffer = 128M max_allowed_packet = 16M thread_cache_size = 8 myisam-recover = BACKUP max_connections = 1024 query_cache_limit = 2M query_cache_size = 128M expire_logs_days = 10 max_binlog_size = 100M key_buffer_size = 128M join_buffer_size = 8M tmp_table_size = 16M max_heap_table_size = 16M table_cache = 96 Other : > vmstat procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 9 0 73140 36336 8968 1859160 0 0 42 15 3 2 6 1 89 5 > /etc/init.d/mysql status Threads: 49 Questions: 252139 Slow queries: 164 Opens: 53573 Flush tables: 1 Open tables: 337 Queries per second avg: 61.302. Edit Additional info.

  Read the article

• MySQL consuming all system memory on INSERT ... SELECT

  - by siete

  The mysql daemon is getting killed because Linux is reaching out of memory: Oct 24 07:41:23 <hostname> kernel: [82297.673701] Out of memory: kill process 13816 (mysqld) score 1839626 or a child There is a link with some workaround on this. That only happen when executing a query INSERT ... SELECT with a very huge resulset. MySQLTuner script displays that maximum theorical memory is less than 8GB, but top and munim shows that is getting over all RAM and swap available: [--] Total buffers: 560.0M global + 72.2M per thread (100 max threads) [OK] Maximum possible memory usage: 7.6G (43% of installed RAM) I'm tried to tune some options with not results, there are the relevant ones: skip-locking max_connections = 100 key_buffer_size = 512M max_allowed_packet = 32M table_open_cache = 2000 open_files_limit = 3000 sort_buffer_size = 16M read_buffer_size = 16M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 4 query_cache_size = 16M query_cache_limit = 2M thread_concurrency = 4 join_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 32M query_cache_limit = 8M bulk_insert_buffer_size = 64M myisam_max_sort_file_size = 50GB myisam_mmap_size = 10GB And there is a system resume: OS: Linux Debian "Squeeze" 6.0.8 (upgraded yesterday) RAM: 18GB Swap: 18GB MySQL: 5.1.72-2 (official Debain release) At this moment, update or change OS or MySQL version is not possible, there is any option that can help and i missed? Sorry by my english, and thank you in advance! Edit: I'm only using MyISAM tables, and cannot change to InnoDB.

  Read the article

• python mysqldb - mysql server gone away - can't reconnect

  - by david.barkhuizen

  When attempting to import a bunch of data into mysql tables using python and mysqldb, I run into the following error '2006 - mySQL Server has gone away', and then I am unable to reconnect again within the script. I am iniitially re-using a connection object across transactions ( delineated by conn.commit() ), then when I first encounter this exception, if I create a new connection by calling MySQLdb.connect(), this new connection also fails with the same exception. This error does not occur immediately, I can pump a fair amount of data into the db, but then faithfully occurs after I have inserted a couple thousand records, so roughly once the db has committed a certain transaction volume, it always falls over like this. If I rerun the script, WITHOUT restarting the db server. then it resumes where it left off, pumps in some data, then falls over again. Before recommendations to change time-out timings, does anyone know why I am not able to establish a new connection after the initial failure ? - Even if I try a couple of times waiting a couple of seconds between each. (btw, I'm running Windows 7, mysql server 5.1.48, mysqldb 1.2.3.gamma.1, python 2.6)

  Read the article

• debugging connection to mysql from python script using MySQLdb

  - by timpone

  I am a python newbie and have a python 2.5 script that is using MySQLdb to connect on OS X 10.5.8. I haven't been able to succesfully connect to the database of interest with this. However, I am able to connect using php's mysqli and also via the mysql cli interface. I get the error: File "build/bdist.macosx-10.5-i386/egg/MySQLdb/connections.py", line 188, in __init__ _mysql_exceptions.OperationalError: (1045, "Access denied for user 'arc_development'@'localhost' (using password: YES)") On my linux box which has the same mysql perms, the script works fine logging in. On my OS X laptop, I am able to create a database named test_python which bypasses mysql authentication scheme. This makes me think that issues like 32bit / 64bit incompatabilities aren't occuring. If I turn on the query log, I get access denied: 100610 20:56:55 4 Connect Access denied for user 'arc_development'@'localhost' (using password: YES) I'm a little bit at a loss to what to do next. Is there any way I can specify in the general log or binary log to get the actual password set on the connection string? How about writing out from connections.py file the value (although not sure how I'd do that)? thanks

  Read the article

< Previous Page | 85 86 87 88 89 90 91 92 93 94 95 96  | Next Page >