Hot answers tagged

30

(Note: Not much of this is relevant to readers using PostgreSQL 9.2 or above from the EDB installers, which now have a greatly simplified default install using the NETWORK SERVICE, though you can still configure other accounts). I have used net user postgres postgres to reset the password for my database but instead of a success message I am getting "...


27

You've just run into a peculiarity of psql on Windows. In my comment I just wanted to exclude the possibility that you really didn't have the necessary permission. Then it turned out that even on Windows, you have to use 'normal' slashes instead of backslashes, that is: \i 'C:/Users/Work/Desktop/School Work/load_database.sql' should work fine ...


26

From your paths, I can safely assume the following: You are running MySQL in Windows You used the MySQL MSI to install What you need to do is establish the file my.ini When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1. Please run the following in a DOS Window: cd C:/...


18

From the 9.0 FAQ, I lost the database password. What can I do to recover it?: I lost the database password. What can I do to recover it? You can't. However, you can reset it to something else. To do this, you Edit pg_hba.conf to allow trust authorization temporarily - go here for its location Reload the config file (pg_ctl reload) Connect ...


18

SQL Server instances have separate sets of binaries for each installation. For the base engine, you will have separate SQL Server and SQL agent services that can run under separate accounts. They each have their own configurations and users. This is also true for other components like Analysis Services and Reporting Services if you install them. There ...


17

PROBLEM In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema. mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | | slave_master_info | | slave_relay_log_info ...


16

I had the same issue installing 9.1.4 on Windows 7. I managed to find a solution online that worked. The steps I followed are: Uninstall PostgreSQL Delete the postgres user if it still exists. net user postgres /delete Create the postgres user with a password you can remember net user /add postgres <password> Add the postgres user to the ...


16

Looking at the my.ini, I have two suggestions SUGGESTION #1 I would bump up the following settings in your my.ini sort_buffer_size=4M join_buffer_size=4M This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table. If you cannot login as root@localhost, then restart ...


16

In Control panel open Administrative tools then double click Services. Scroll down to MySQL and double click to open the properties. Change the Startup type to Manual.


15

Think of the data file as a container, like your fridge. The fridge doesn't tell you when the milk is bad (ok, maybe the Jetsons' fridge does) - you need to check inside. For SQL Server, the timestamp in Windows has nothing to do with the data you are changing inside the file; it has to do with the last time the file itself has changed, such as a service ...


14

You definitely need to know your way around... Performance Monitor Disk Management (mount points, 64k clusters, RAID levels etc) Group Policy + Active Directory + Security Shares + NTFS permissions Cluster Service + resource groups Powershell Physical/Logical sockets/cores + affinity NUMA architecture Without these (and others) you're ineffective outside ...


14

this bug is reported in MySQL Forums (67179) and the solution is mentioned here. please note that you should do clean-up, before creating tables /* temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines. I did the procedure on a clean installed MySql, and it worked for me, at least it stopped lines of innodb errors in ...


14

Is there any reason NOT to use the approach you've defined? Absolutely. Imagine you had bought a car - a car that when you hit 50MPH the engine starts to overheat. Would your reaction to this situation be to artificially limit the car to 49MPH, or to find out what the fault is with the engine? Why should you limit your car to 49MPH? The manufacturer stated ...


13

The words "securely", "exposing", "outside" and "database" do not belong together. Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea. So your question really should be: How do we give access to our SQL server to remote locations? The answer: VPN. The remote users should establish a VPN connection to your ...


13

I answer my own question here for completeness I will select @RolandoMySQLDBA as the preferred answer because it gave me the most hints even though it didn't actually solve my problem. Below are the results of my investigation Conclusion MySQL on Windows just creates lots of temporary tables and tuning MySQL by modifying the content of the configuration ...


13

Why 52395 instead of 1433 Named instances listen on dynamic ports. Is the job of the Sql Server Browser Service to inform the clients of the actual port. The Sql Browser listens on UDP 1434 and answers questions of the form 'what is the listenning port of instance "foo"?'. Sql Server Browser service is required for both TCP and named pipes protocols. ...


12

I have some queries you can run against the INFORMATION_SCHEMA Run this to get the Total MySQL Data and Index Usage By Storage Engine SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( FORMAT(B.ISize/POWER(1024,pw),3),',','...


12

Locate your postgresql.conf in the datadir Find the section that looks like this #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - #log_destination = 'stderr' # Valid values are ...


12

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by: it did not make the actual MySQL setting changed I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine): The global variable change ...


12

What made my SQL server listen for TCP connections on port 1433 was the following... Configuration Tools / SQL Server Configuration Manager click the instance name in SQL Server Network Configuration enable TCP communication Right-click the TCP communication entry, choose Properties Click the IP addresses tab Enable all non-VMnet IP addresses and enter 1433 ...


11

Create matching usernames and passwords. It really is that simple. For your example, create an account for User1 on Server1 with the same password as on PC1. Just in case this is an issue you're having with connecting SSMS with a non-domain account, that requires jumping through an additional hoop. Some applications don't support stored usernames and ...


11

SIDs in the form of 0x01020000000000052000000021020000 are not "SQL Server" SIDs. That is simply the underlying binary value of the SID. Another form it can take (and still be the same value) is the "string" form (SID String Format Syntax), which looks like S-1-5-32-545 (referred to as being the "SDDL" format in some MSDN documentation, though SDDL covers ...


11

Erik Darling mentioned the biggest practical reason for not using WSRM in a comment on your question: ...there's no reciprocal limiting of CPU use in other processes. SQL Server may not use those two cores, but other things may use the other 30 SQL Server is using. It's a crapshoot, really. If this is working for you, then stick with it - we're all busy, ...


10

Here is the problem. The OS has two modes to print things stdout stderr The --silent only affects stdout. How do you nail stderr? Try one of the following, and see if it works: mysql --user=myusername --password=mypassword --silent --force -b 2> nul mysql --user=myusername --password=mypassword --silent --force -b --tee=nul Give it a Try !!! CAVEAT ...


10

There is nothing MySQL has that will indicate the progress of OPTIMIZE TABLE;. You may have to go the OS using Windows Explorer and look for a growing tmp table. Hint: Temp table are MyISAM tables that do not have a .frm file with the name #sql-9999.MYD and #sql-9999.MYI. While these two fle exist, the query would be considered in progress. To know how far, ...


10

Since you're on Windows and are probably starting PostgreSQL as a Windows service, you'll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command. You can use a programmatic text editing tool to change postgresql....


10

Open Windows CLI as Administrator and Shutdown MySQL net stop mysql Copy the Data to D:\ProgramData\Data xcopy /s C:\ProgramData\MySQL\MySQL Server 5.6\data D:\ProgramData\Data Create or edit C:\ProgramData\MySQL\MySQL Server 5.6\my.ini Add this to the my.ini [mysqld] datadir = D:/ProgramData/Data Startup MySQL net start mysql If you can not execute ...


9

No. As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server. The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access. SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it ...


9

Here is a start, if I can guess that by row you actually mean column: SELECT [table] = QUOTENAME(OBJECT_SCHEMA_NAME([t.object_id])) +'.'+ QUOTENAME(t.name), [column] = c.name FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id] ORDER BY t.name, c.column_id; This doesn't provide exactly the output you want, but surely your ...


9

There are a number of options and please don't limit yourself to my answer here. In particular you may find array-native databases to be of help. My answer is going to be specifically about your questions on SQL-based databases. It sounds to me like this is a question of geospacial information. SQL-based databases are in fact used in such fields quite ...


Only top voted, non community-wiki answers of a minimum length are eligible