MySQL auto increment column increases after insertion error occurs I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row inserted in this statement.

Thereby, this behavior causes the next valid insertion skip an available and possible value for the auto_increment.

Example:

1st Insert | Result: Success | id(auto_increment_column): 1       |
2nd Insert | Result: Failure | -------- No rows were recorded---- |
3nd Insert | Result: Success | id(auto_increment_column): 3       |

I want to know if someone had this problem before and if this is really a problem.

Cheers.

P.S.:

Errno: 1452 - fk constraint fails. This means that I'm trying to reference some column value in another table and this value doesn't exists there.

Database: MySQL Ver 14.14 Distrib 5.6.13, for Windows 64-bit
Storage Engine: InnoDB

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says

  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

Therefore, if the second INSERT had failed and rolled back, it makes the gap.

You may have to set innodb_autoinc_lock_mode to 0 or 2. Please read the MySQL Documentation on Configurable InnoDB Auto-Increment Locking to understand what changing innodb_autoinc_lock_mode can do for you.

Where your table design is
id     int auto_increment primary key,
field1 int,
field2 int,
unique u_f1_f2 ( field1, field2 )

You can try insert like :
insert ignore into table select (select max(id)+1 from table), "value1", "value2" ;

This will try

insert new data with last unused id (not autoincrement)
if in unique fields duplicate entry found ignore it
else insert new data normally
( but this method not support to update fields if  duplicate entry found )


Database after restore process still display Restoring I restored Database from Backup File (in Management Studio) and after restore, Database showed Restoring, then I deleted files from disk (engine's stopped), then I tried Restore again with success, on both cases I leave Restore Process on Default Options

I have no idea why, dump file I created after restart of enviroment, without active Connection(s)

my question: is possible somehow to stop the Restoring status, if is there some tools for that

By default, a DB is not ready to use. It stays "restoring" ready to receive log restores.

Simply run this to bring the DB on-line

RESTORE DATABASE MyDB WITH RECOVERY

Or using the GUI, this is step 12 in the MSDN SSMS "how to" (a different place to what you mention)

So there are three ways you can restore the database

No recovery RESTORE DATABASE MyDB WITH NORECOVERY
Recovery RESTORE DATABASE MyDB WITH RECOVERY
Standby RESTORE DATABASE MyDB WITH Standby

Thanks,
Piyush Patel


How to create a conditional index in MySQL? How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.

Example: I want to create an index for NAME column just for rows with STATUS = 'ACTIVE'

This functionality would be called a filtered index in SQL Server and a partial index in Postgres.

MySQL currently doesn't support conditional indexes.

To achive what you are asking (not that you should do it ;) ) you can start creating an auxiliary table:

CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

Then you add three triggers in the main table:

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

  We need delimiter // because we want to use ; inside the triggers.

That way, the auxiliary table will contain exactly the IDs corresponding to the main table rows that contain the string "ACTIVE", being updated by the triggers.

To use that on a select, you can use the usual join:

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

If the main table already contains data, or in case you make some external operation that changes data in an unusual way (E.G.: outside MySQL), you can fix the auxiliary table with this:

INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

About the performance, probably you'll have slower inserts, updates and deletes. This can make some sense only if you really deal with few cases where the condition desired is positive. Even that way, probably only testing you can see if the space saved really justifies this aproach (and if you are really saving any space at all).

I know this question is fairly old, but the best approach for this would be to just create the index with the two columns in the correct order. If you place status first in the index and then name, you will be creating a index that would "group" all of your 'ACTIVE' names. That way when you query against the table, the optimizer would know exactly where in the index to start looking for the name you're querying against.


Cannot connect to a SQL Server 2014 Express database intermittently I have problems connecting computers running Windows 7 to a database SQL Server 2014 Express intermittently.

It happens that, intermittently, I can not connect to the database that is on a Windows 2008 R2 server.

If I restart the computer, works again, but only for a few minutes, sometimes hours.

The Firewall of my computer is turned off. Windows Firewall is turned off.

CONFIGURATION:

The server is Windows Server 2008 R2 Foundation
Firewall Ports TCP / IP and UDP 1434 1433 allowed

SQL Server Express:

Unlimited Connections
Allows remote connections
Timeout remotely query: 600 seconds

Machines with Windows XP have no problem with this issue.

Finally, it seems to have turned the problem: an IP in the secondary DNS configuration caused the problem.
Removing the IP, everything works correctly again.

Make sure your power settings are not set to hibernate or sleep on the client PC's (Check ALL power option settings, place a value of '0' for 'Never'). Typically if the machine goes to sleep, the ODBC connection to the DB gets lost. That is why 'restarting' will work, and the time frame varies from a few minutes to a few hours.

Also, make sure you have both ODBC 32 bit and 64 bit configured for your DB.

Typically located on...
c:\windows\syswow64\odbcad32.exe but easily accessible in Control Panel under Administrative tools.

I hope this helps you solve your issue. It certainly could be a cause of the issue described, feel free to add on to this issue if my solution does not resolve your issue.


How do I list all schemas in PostgreSQL? When using PostgreSQL v9.1, how do I list all of the schemas using SQL?

I was expecting something along the lines of:

SELECT something FROM pg_blah;

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA

select schema_name
from information_schema.schemata;

More details in the manual

alternatively:

select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual

Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction.
Here's how the trick goes.
Open one postgres session, then type your command :

begin;
\dn+

While the transaction still running, open another postgres session, and query the pg_stat_activity and you can get the exact sql.

postgres=# select query from pg_stat_activity ;
                                 query
-----------------------------------------------------------------------
 SELECT n.nspname AS "Name",                                          +
   pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",                 +
   pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",+
   pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description" +
 FROM pg_catalog.pg_namespace n                                       +
 WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'      +
 ORDER BY 1;


PostgreSQL: Extending database storage when disk is full I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore.
I have read the documentation about tablespaces link But It's like create space for a new table, whereas I want to continue insert into exist table. Could somebody help me out?

In addition, PostgreSQL database maximum is 64TB, so we have to connect many HDDs to achieve 64TB, then the solution could be apply to this situation (many connected HDDs or shared storage, I'm not sure how to say)

Thanks in advance!

PS: I'm working with postgreSQL 9.2 on ubuntu 12.04 LTS

PostgreSQL doesn't support spanning a single table over multiple tablespaces directly.

You can use table partitioning to create a "master" table, with child tables on different tablespaces. It's a bit tricky and it means you can't use foreign keys or enforce a proper unique constraint on the primary key, so it's not generally the preferred option.

A more practical option is usually to use a logical volume manager, RAID system, or similar to span multiple physical disks into a single volume, with a single file-system on it. Then PostgreSQL just uses it like anything else and doesn't need to care. On Linux, this might mean LVM, the md software RAID driver in raid0 mode, btrfs, or all sorts of other options. Most (all) of these require you to build a new filesystem then move the PostgreSQL database onto it, you can't convert a regular filesystem that's directly on top of a partition into a raid volume / logical volume / etc.

Note that neither of these options are safe if the additonal storage is removable. If you remove a disk containing a tablespace, or remove part of a logical volume or spanned raid, your entire database will become inacessible. If you lose the disk/volume or its data, the entire DB will be unrecoverable.

You can run vacummn analyze command on the table so that it release the stale memory(not in use or space required by table for rows which are deleted) which can be used to add new row into table.


Restore database with zero size log file SQL Server backups contain meta-data about the size of the original log (LDF) file.

So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. This often isn't needed (because the log file of the restored database contains practically nothing)

Often this causes problems with database restores on busy dev servers with not much space left if people *cough* haven't been too careful about shrinking log files before making the backup.

Is there any way to restore a SQL Server backup but tell it "Dont bother making that empty 5GB LDF file, thanks" ?

No, there is no way. Restores really is meant to recreate the original database that was backed up. There are tools that allow you to query directly a backup w/o actually restoring it (Idera's SQL Virtual Database) but those are horrendously slow.

Well you might have to go through another step or two: restore on another server, shrink, backup, copy and restore. If you restore in simple recovery mode (or switch to simple recovery mode which can be done with the db online), the log database will be (or can be shrunk to) as small as it can get. You can then switch to full recovery when the finance department decides that the potential data loss cost is more expensive than the additional storage.... In SQL Server Management Studio, Rt mouse click on the database and select properties, then go to the options tab, second drop down list from the top. Then go to File sizes and shrink.... Wasn't even difficult, might be time consuming...


adding cluster index other than primary key default cluster index create table t1 (id int primary key auto_increment, name varchar(20))

In the above table id is primary key and by default cluster index is added to primary key column.

My question is, can we add cluster index on column (name) too??.

can we add cluster index on column (name) too?

No. Only one index is clustered.

For MySQL:

If primary key exists it is clustered.
If no primary index but unique indexes exists then first unique index is clustered.
If no unique indexes exists then the index by inner record number (hidden, unaccessable) is clustered one.

Note:

Yes, You can add cluster index on column (name) But it's once time
   because in one table have only one cluster Index in SQLServer.


New and old passwords on same MySQL database? Is it possible to set up a user in MySQL 5.5 with a new password whilst keeping existing users with the old type of passwords? By old and new passwords I mean the style of hash.

I was thinking I could try specify the hash directly but I'm struggling to get that working (I'm new MySQL).

Unfortunately I can't update all users to the new type of passwords as it would break access to a system I can't control. I also need the default new account to use the old style hash.

It is possible before version 5.7, where it has been disabled.

To set up an old password for a user, use command:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

More information: Password Hashing in MySQL.

You can enable/disable old passwords globally or per session. On a server where they are enabled globally (i.e. the server was started with old_passwords = 1), run this:

SET @@session.old_passwords = 0;
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('mypass');

to disable them for a session. The PASSWORD in the above snippet will return a password in the new format, affecting only the current session.

First you should stop or disable all the services if any other database is active on your system, then you can do it with an active instance of MySQL.


SQL join on col1 if present else use col2 There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.
What I want is either join on T1.col1 = T2.col1 or T1.col1 = T2.col2 if col1 is NULL in T2.
I have already tried these:
select * from T2 left join T1
on T1.col1 = coalesce(T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 in (T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 = T2.col1
or T1.col1 = T2.col2)

Which result in the ETL job never ending.
Additional info:

Redshift DB
No indexes because columnar DB
T2.col1 and T2.col2 will be ditto. T2.col1 comes from upstream, whereas T2.col2 is derived from the ETL. Until upstream quality improves, we'll need fallback on col2.

The problem here is that using a function or or around your join predicate means that the database engine can not use any indexes that you might have defined on the relevant columns for seeking so it is forced to table scan. On a large table this can take a long time. Worse, if this is part of a larger query you might find the scanning must be done multiple times.
If you have useful indexes on T2.col1 and T2.col2 (and T1.col1) then the following is likely to be more efficient, assuming that what you actually want is an INNER JOIN:
-- find matches for col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col2 AND T2.col1 IS NULL

Because each of the UNIONed SELECTs can avoid table scanning this will be more efficient than the single SELECT, assuming appropriate indexes exist for them to use.
If you do want a LEFT JOIN (i.e. you want to include rows from T1 that don't match those in T2 at all) then you need an extra clause for this:
-- find matches for col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col2
          AND T2.col1 IS NULL -- this will match above and you'll get a duplicate
UNION ALL
-- find rows in T1 where there is no match in T2
SELECT *
  FROM T1
  LEFT JOIN
       T2 ON T1.col1 = T2.col2
 WHERE T2.col1 IS NULL
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

If you were not using SELECT * (i.e. had a fixed column list instead of the wildcard *) then you might find that third clause easier to understand as:
   SELECT [columns-to-project]
  FROM T1
 WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.col1 = t1.col1)
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

(whether this would be more or less efficient, or exactly the same, depends on your database's query planner and the indexes you have defined).
Note that the first SELECTs assume that you have no rows in T2 with a col2 that matches T1.col1 = T2.col2 and also have a col1 that matches T1.col1 = T2.col1, and that there are none with a valid T2.col2 but a T2.col1 whcih does not match any T1.col1. If such rows do exist then extra work is needed, I've not included that because your current queries would skip those matches just as my examples will.
Note also that any claims of efficiency of the above examples relies on appropriate indexes existing.
For a more concrete answer you will need to edit your question to provide:

The table structure, particularly what indexes are defined.
A more detailed description of your data, particularly with respect to what goes in T2.col1 & T2.col2 and when.
What database system you are using (there might be specific features in your DB that could help with this sort of query and table design), unless you particularly need a database-agnostic answer.

I'd approach this problem by writing a SELECT statement on T2 which selected either col1 or col2, like:
SELECT  col_to_join = coalesce(col1, col2)
FROM    T2

Then use this SELECT statement in the join.


Proper use of database functions for business logic I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.

These functions are called by an application in java that has almost no business logic in the code.

This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.

This approach where most of the logic is in the database is correct?
If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To don’t delete or replace a change in client database by error?

Two queries - two replies:

a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):

natural decomposition of your application between data processing and data presentation layers
very well accessible API in heterogeneous environment - logic in database is accessible from shell scripts, all languages, ...
very efficient data processing due closeness data and processing unit (in PostgreSQL it is same process). It removes a network latency, conversions between protocols, layers, drivers - PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.
you can create a API based on stored procedures, so any changes on database layer are transparent for you and for your application.

Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.

b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.

Personally, I don't like the solutions which put business logic into the database. My experience is the business logic is not always stable, sooner or later it will change. So your database functions or stored procedures will have to be modified, even the table structure will change sometimes. It's not easy to trace and debug inside the database. That's why we will use multi tier design: database, data access tier, business logic tier, presentation tier.


Can I use a foreign key index as a shortcut to getting a row count in an INNODB table? I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;
+----------+
| COUNT(1) |
+----------+
| 25453476 |
+----------+
1 row in set (2 min 36.20 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
|  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.

You cannot trust the EXPLAIN plan's row count. Why?

Each time Query Optimization takes place where it involves an InnoDB table, the InnoDB Storage Engine does an approximation of the BTREE entries for the keys. They approximations change from query to query.

Simply run SHOW INDEXES FROM guage_data; multiple times and you will get a different set of row counts each time. You could set innodb_stats_on_metadata to 0:

SET GLOBAL innodb_stats_on_metadata = 0;

This will cause SHOW INDEXES FROM guage_data; to give the same number over and over again. This is still self-defeating because the row count would still be approximated anyway. You would have to disable the metadata stats and run ANALYZE TABLE. Again, this is self-defeating.

With innodb_stats_on_metadata on, approximations are always done. I wrote about this back on June 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

In light of all this, you are better off doing SELECT id FROM guage_data if id is the PRIMARY KEY. However, notice in your EXPLAIN plan that MySQL choose to traverse the gauge_data_FI_1 index. This makes all the sense in the world if this table is InnoDB. Why? Every non-unique index stores the rowid of the PRIMARY KEY anyway.

This behavior seems very strange to me.  I don't have any MySQL databases with tables this size, but COUNT(*) on my Informix and MS SQL Server databases that have tables even larger than this run instantaneously.

How exact do you need it to be?  If inaccurate but reasonable estimates are fine, can you use this:

SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'gauge_data'

Source


Permission error when executing a 5.6 GB sqlcmd file I am trying to run a very large file through the sqlcmd command and it works perfectly with files of 100 MB up to 1.4 GB. This is what I have tested so far, but when I try to run a file which is 5.6 GB, it throws this permission error:

  Sqlcmd: Error: Error occurred while opening or operating on file C:\Test\Insert_Generated_File.sql (Reason: Access is denied).

The error is thrown immediately.

Are there any limitations on SQL Server Express edition when executing large files?

Just so that everything is clear, the file contains INSERT generated statements from a 4GB database and nothing more. It is saved as a *.sql file.

The version of sql I'm using is:

  Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
      Jun 10 2015 03:35:45
      Copyright (c) Microsoft Corporation
      Express Edition (64-bit) on Windows NT 6.3  (Build 10586: ) (Hypervisor)

All I want to know is why am I getting that error, is it because of Express Edition, or because sqlcmd cannot handle a file that large. Unfortunately, I do not own a Standard Edition of SQL Server so I cannot test it there.

I know of the SQL Express database limitations, yet I am scripting a database which is already part of my server. It would make sense if I did not have this data already in, but I do. And all the data is already in once. I just cannot recreate it using this file.

@Mihai, As  Daniel Hutmacher already said,Express Edition is limited in database size. As per sqlmag BOL http://sqlmag.com/sql-server-2012/sql-server-2012-express-editions, if you are using SQL Server Express with Advanced Services. It is limited to 4GB and can access only the local instance.

Even in dba.stackexchange the Shanky How many users are supported on SQL Server 2012 Express? has also explain the limitation of SQL Server Express edition .


How do you correct a corrupted index in Oracle? I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ ) I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

ERROR at line 1: ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up. What am I doing wrong? This is a 10.2 Oracle server.

Thanks in advance!

EDIT If I do MYSRVR.PK_ZXCATSET_CATID  the alter completes... but I still don't think my problem is fixed!

Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:

[oracle@oca ~]$ rman target=/

RMAN> list faliure;

If there are failures with status OPEN listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:

RMAN> advise failure;

Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:

RMAN> repair failure;

After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure command in RMAN, and if not you can get another advice from DRA.

EDIT:

Since the recovery you performed didn't last long (elapsed time: 00:00:00), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.

In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG mode (check with select log_mode from v$database) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.

alter index "MYSRVR"."PK_ZXCATSET_CATID" rebuild tablespace MYSRVR_IDX_TB;

or alter index MYSRVR.PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;


How to move a database from SQL Server 2012 to SQL Server 2005 What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?

I know I cannot:

restore a backup of the database on SQL Server 2005
detach & attach

I know I can:

use import data wizard, and I tried it on one database, but it only moves data, and even that was troublesome as I needed to do a lot of work creating temp tables to maintain identity columns, recreating all FKs, indexes etc.

Is there an easier option?

It's impossible to downgrade a database on SQL Server. However, a workaround is to script all DB objects and apply the script on the destination database. SSIS is a great way to help perform this.


help in connecting the sql server 2017 developer edition I have installed the sql server 2017 for non production environment in my PC.
It has installed successfully.
when connect using sqlcmd it was throwing an error login failed for login sa. it didn't have the asked for the creating user or anythings...
please find the command used to connect

sqlcmd -S servername -U sa

Update:

I can't even log in using windows authentication.

Update2:

I have tried to access sqlcmd from command prompt i got the following error.

  Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [2]. . Sqlcmd:
  Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout
  expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

It sounds like one of the following things is happening:

The sa account is disabled

either explicitly, or because you're in Windows Authentication mode

You don't know the password to the sa account

Since this is installed locally on your PC, you should actually be able to connect with sqlcmd without any parameters to use "Trusted Connection" to authenticate you:

sqlcmd

Once you're in, you should be able to check the authentication mode using an approach like this one:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

If that returns "Windows Authentication," then you'll need to go through the process of switching to mixed mode outlined here: Change Server Authentication Mode

If the query above returned "Windows and SQL Server Authentication" then it's possible that you have just mistyped or forgotten the sa account password, or it's disabled for some other reason.  In that case, you can run this code (also included in the docs link above):

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

use osql instead of sqlcmd heres where i found it, it worked for me

https://support.microsoft.com/en-ph/help/307864/error-message-when-you-connect-to-an-instance-of-sql-server-cannot-ope


Getting "ORA-00942: table or view does not exist" while table does exist I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using:

$ sqlplus "/ as sysdba"

I successfully created a table and inserted some data and performed some selects:

SQL> CREATE TABLE Instructors (
         tid    NUMBER(7) PRIMARY KEY,
         fname  VARCHAR2(32),
         lname  VARCHAR2(32),
         tel    NUMBER(16),
         adrs   VARCHAR2(128) );

Table created.

SQL> INSERT INTO Instructors (tid, fname, lname, tel, adrs)
     VALUES (8431001, 'John', 'Smith', 654321, 'London');

1 row created.

SQL> SELECT count(*) FROM Instructors;

  COUNT(*)
----------
        1

Then I created a new user with the CONNECT privilege:

SQL> CREATE USER teacher1 IDENTIFIED BY pass1;

User created.

SQL> GRANT CONNECT TO teacher1;

Grant succeeded.

Then I created a new role with appropriate object privileges:

SQL> CREATE ROLE instructor;

Role created.

SQL> GRANT SELECT, UPDATE ON Instructors TO instructor;

Grant succeeded.

And granted the role to the user:

SQL> GRANT instructor TO teacher1;

Grant succeeded.

Next I exited sqlplus with exit; and connected as the new user to test it. I logged in successfully to the database with:

$ sqlplus teacher1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 25 03:20:50 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to select from the table it says:

SQL> SELECT * FROM Instructors;
SELECT * FROM Instructors
              *
ERROR at line 1:
ORA-00942: table or view does not exist

What I'm missing here ?!

You created the table in the SYS schema (which you should never, ever do. Really, never).

When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?).

You need to run select * from sys.instructors to get access to that table. If you don't want to prefix the table name with the schema, create a synonym in the teacher1 schema:

create synonym teacher1.instructors for sys.instructors;

Then teacher1 can access the table from the SYS schema without fully qualifying it.

Again: stop using the SYS or SYSTEM account for anything that is not DBA stuff. Use a regular account for that.

my answer is just write table name with login credentials of "sys" or/and "xe"
like
for my program change
ur table name is EMP then write XE.EMP
like "select * from XE.EMP"


Getting Index Scan instead of a possible Index Seek? Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue".

I am using the AdventureWorks2014 database, I ran this simple query:

table structure (taken from https://www.sqldatadictionary.com/AdventureWorks2014.pdf):

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

returns 19,720 rows

total number of rows in Sales.Customer = 19,820

And after checking to make sure CustomerID is in fact not just the PK of the table but also has a clustered index on it (yet it uses a non clustered index), that indeed is the case:

EXEC SP_HELPINDEX 'Sales.Customer'

Here is the execution plan ↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

I have read that when faced with huge amounts of data and/or when it returns more than 50% of the data set, the query optimizer will favor an index scan. But that table as a whole barely has 20,000 rows (19,820 to be exact), it's not a big table by any means.

When I run this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

returns 118 rows

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

I get an index seek instead, so I thought it was due to that "more than 50% case" however, I also ran this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

returns 10,118 rows

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

And it also used an index seek, even though it was returning more than 50% of the data set.

So what's going on here?

EDIT:

With IO Statistics turned on, the >100 query returns:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While the >20,000 one returned:

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So added WITH(FORCESCAN) option to the >20,000 one to see what would happen:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it ends up running better with an Index Scan (less logical reads), even though the query optimizer chose to run an Index Seek for this particular query.

You use a nonequality predicate so your "seek" operations are in fact scans which just start from some value (not from "first") and then go to the end of the clustered index leaf level.

In the other hand you return only one column which is the clustered index key so using any of indexes won't get any key lookup operations. The optimizer has to estimate what would be cheaper: scanning a nonclustered index (two int columns on the leaf level) or part scanning of your clustered index (all columns on the leaf level).

It estimates it depending on current statistics (how many rows) and metadata (what is one row size). We saw the optimizer made a mistake on >20,000 predicate.

  When faced with huge amounts of data and/or when it returns more than
  50% of the data set, the query optimizer will favor an index scan.

That is a fact when the optimizer has to choose performing clustered index or table scan versus nonclustered index seek + key lookups.

In your case if your index on CustomerID were nonclustered you would always see a seek operation on that index, but if you then added another column to your output you would see the index seek + RID lookups on short resultsets and table scan on the big ones.

The optimizer uses whatever it thinks is fastest; a lot of times what I'd expect it to do, it doesn't.  It's not just based on row %; it's based on lots of factors like the statistics it has, indexes, the table columns and query itself.  Uses that to create cost and threshold estimates, although number of rows does come into play.

I'd guess it scanned the first query because of the factors aforementioned, namely statistics. It did index seek on the second for the same reason.  The third seek might have just been because plan was already compiled in memory.  I'm curious if it would have scanned for that if you tried it with recompile like @scsimon suggested.


How do I access Postgres when I get an error about "/var/run/postgresql/.s.PGSQL.5432"? I am running Ubuntu 16.  I have installed Postgresql.  Postgresql used to work, but then I rebooted.

nmap commands show port 5432 is open.  Postgres seems to be working correctly:

service postgresql status

  postgresql.service - PostgreSQL RDBMS    Loaded: loaded
  (/lib/systemd/system/postgresql.service; enabled; vendor preset:
  enabled)    Active: active (exited) since Sat 2017-07-29 18:42:59 EDT;
  1min 4s ago   Process: 201 ExecStart=/bin/true (code=exited,
  status=0/SUCCESS)  Main PID: 201 (code=exited, status=0/SUCCESS)
  Memory: 0B    CGroup: /system.slice/postgresql.service

I ran this: psql

But I got this:

  psql: could not connect to server: No such file or directory
          Is the server running locally and accepting
          connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The file listed above does not seem to exist.

How do I get into Postgresql?  Normally I'd run psql or sudo -i -u postgres then psql.  But these commands are not working.  I keep getting an error about "could not connect to server."  Several reboots have not helped.

Update:

I ran this command: dpkg -l | grep postgres

rc postgresql-9.5                9.5.6-0ubuntu0.16.04                   amd64        object-relational SQL database, version 9.5 server
ii postgresql-client             9.5+173                                all          front-end programs for PostgreSQL (supported version)
ii postgresql-client-9.5         9.5.7-0ubuntu0.16.04                   amd64        front-end programs for PostgreSQL 9.5
ii postgresql-client-common      173                                    all          manager for multiple PostgreSQL client versions
ii postgresql-common             173                                    all          PostgreSQL database-cluster manager

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case . Try restarting Postgres clusters and service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw an error

If this process is not successfull it will throw the error.
My error was(You can see the error log on /var/log/postgresql/postgresql-9.6-main.log)

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main
eg: sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check Postgres user belongs to ssl-cert user group

It happened to me and it turned out that I removed erroneously the Postgres user from "ssl-cert" group. Run the below code to fix the user group issue and fixing the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fixed ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

sudo service postgresql restart

Remove the database and user access
goto this path and open pg_hba.conf and remove the user below the "Database administrative login by Unix domain socket" other than postgres database.

/etc/postgresql/10/main$ sudo nano pg_hba.conf or sudo nano /etc/postgresql/10/main/pg_hba.conf

after removing the user we got like this:


Multiline UDF or Stored Procedure for SSIS Data Source We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow.  Would utilizing a stored procedure produce any faster results when streaming data from one place to another?

Table Valued Function Very Slow

It will always depend on the logic being implemented but I'd consider at least the following items when choosing between the two object types as a SSIS data source...

Are you planning to modify data in a permanent table as part of the logic? You may be restricted to using a stored procedure if you need to perform interim DML when preparing the data source.
Will your final result come from a SELECT from a temp table? It becomes more tedious to use a stored procedure as a data source as the temp table isn't created until sproc runtime; SSIS therefore can't see the table structure for column mappings in the data flow. Workarounds include using retaining connections at package level, utilizing global temp tables and delaying/bypassing validation of the package and affected data flows. It's a bit of headache compared to a table-valued UDF where you can define the output table structure up-front.

Table Functions are known to be uneficient and SQL Server is slow in general when you launch something based in one or a few records and the total sets is processing several records.
I would propose you instead to re-write as a subquery based solution.
Paste your code and we can try to help you.


Counting rows from a subquery Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not.

Bad code

SELECT COUNT(ip_address) FROM `ports` (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
)

Explained

The first query, when run on its own returns this:

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

ip_address
192.168.1.1
192.168.1.2
192.168.1.248
192.168.1.251
192.168.1.254

The second query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`

17

Question

I would like to know how to count that list of 5 IP addresses.

I have been looking online at possible solutions to this simple problem and just getting frustrated, so thought I'd ask the experts.

To answer your immediate question, how to count rows of a subquery, the syntax is as follows:

SELECT COUNT(*) FROM (subquery) AS some_name;

The subquery should immediately follow the FROM keyword. (In MySQL it is also mandatory to assign a name to a subquery of this kind (it is actually called a derived table), which is why you can see the AS some_name following it.) The way you have written it, MySQL interprets your script as two independent queries, that is why you are getting two result sets.

So, since the subquery in your case is

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

the complete query would look like this:

SELECT COUNT(*) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS derived;

But, as Julien has suggested, you can rewrite your query just like this:

SELECT COUNT(DISTINCT ip_address) FROM `ports` WHERE status IS TRUE;

This way you do not need a subquery/derived table at all, because with the DISTINCT keyword the COUNT function will count only distinct occurrences of ip_address in the ports table.

No one pointed out the actual problem with the Bad code so: You need to name the inner SELECT's output using AS some_name
SELECT COUNT(ip_address) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS some_name

As mentioned already, best is:
SELECT COUNT(DISTINCT ip_address) FROM `ports`;

Why?
See: https://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias
On MySQL 5.7 you get ERROR "[42000][1248] Every derived table must have its own alias" without AS some_name. With it, it works.secs ago


Attaching MDF without LDF I only have an .mdf file of my database which is not attaching.

I am using the following command:

EXEC sp_attach_single_file_db @dbname = 'Test_Data',
@physname = N'D:\Test_Data\Test_Data.mdf';

and I get the following error message:

  Msg 1813, Level 16, State 2, Line 1
  Could not open new database 'Test_Data'. CREATE DATABASE is aborted.
  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:24; actual 0:0). It occurred during a
  read of page (1:24) in database ID 11 at offset 0x00000000030000 in
  file 'D:\Test_Data\Test_Data.mdf'.  Additional messages in the SQL
  Server error log or system event log may provide more detail. This is
  a severe error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.

Please help!

Your MDF is actually corrupt, based on that error message. This means you can't attach it.

You could create a database with the same name, substitute MDF (and rename LDF), and hope it comes online or at least suspect to start recovering it.

Otherwise, you'll need to find a 3rd party tool that understands MDF files to get data back.

Or just restore from your latest backup...?

Maybe you should try this. I got the answer from an MSDN thread.

make a copy of your mdf file and change the extention (.mdf) to (.ldf)
attach the mdf file using SSMS (note you have to locate the .ldf file)
if you click ok ( error will occur) thats normal.
select the ldf file and click remove. SQL server will automatically create a new ldf file for your attached mdf file.
click OK now and your done..


Unable to start the SQL Server Agent I am unable to start then SQL Server Agent. I am getting the below error message when I tried to start the agent:

  The SQL Server Agent service (DB_NN) on local machine was started and then  stopped. Some services automatically stop if they are not used by any service or program

SQL version: SQL Server 2008R2 Standard Edition(RTM)
Windows:Windows server 2008 R2 enterprise

I verified below things, currently SQL agent is running through service account. I changed to local and tried to start. But agent didn't started.

How to fix this issue?

After reading your posting I understand that when you establish USBLAN_LDR service on windows server 2008, system prompt an error message.

According the information what you provided right now, it’s hard to determine what cause this issue, you may like to post more information about your environment (Event log ,Other  System prompting etc… ).

Please check if it worked with assign a  Local System account instead of Local Service account to run this service.

Open service MMC snap-in. ·
Right UBSLAN_LDR service , and click “properties”.
Click “Log on” tab , and assign a local account for running this
services.

Seems this issue related to a third party software, I suggest to consult software vendor for future troubleshooting.

Please understand that I don't mean to bounce you between different solution provider as I am fully aware how time consuming this can be. However, they really are in a better position to be able to assist you with this issue as they may have experienced similar issues.


The process cannot access the file because it is being used by another process I'm using SQL Server 2008 R2.  Absolutely new to DBA role, but I've worked with SQL and VS for a while.

I've installed the 2 aforementioned softwares on my home computer.  If I have VS2010 open and try to open the SQL Server database so I can make changes to something, I'm getting the error:

  The process cannot access the file because it is being used by another process.

Now, at work I can definitely have VS2010 open and edit stuff in my database, so I'm guessing there's a setting that allows you to do this?  Can anyone tell me where this setting is, and how to adjust it so that I don't have to exit VS2010 every time I need to write a new Stored Procedure in SQL Server?

just make sure to close the designer window before hitting F5.


last modified rows timestamp for SQL Server 2016 Is there a feature/script to check when was the last time a row has been modified/changed in SQL 2016+ tables?Does SQL track this automatically or do we have a feature which gives this info? Please let me know your thoughts/

Noting done by default. You can turn on stuff like temporal (probably overkill) or create a trigger to update the table.
SQL Server keeps track of all modification internally, in the transaction log, for its own purposes (like rollback and crash recovery). But the log is volatile and even if you get there before the info is gone, you'd have to decode the info yourself using unsupported means (like fn_dblog).
[Edit] Adding in the options Change Tracking (lower overhead) and Change Data Capture (higher overhead and uses an Agent job but gives more info than CT), as suggested in the comments below by Dominique.

You can retrieved everything by fn_dblog as SQL server tracks all the changes in transaction log. But, CDC feature is the best for your case.


Difference between an Entity and a Relation I am currently reading about entities and relations and I can't understand the difference between them? According to Wikipedia we have an example of a relation like in the pic:

This looks exactly like an entity set. What's the difference between an entity and a relation?

"Entity" is a real world concept, such as "a person" in your example.

"Relation" is a set of tuples (records), each representing a model of an entity. In your example each tuple represents attributes of a single person, and their collection is a relation.

Relation is an instance ( a record) of an entity


Can I make sure two columns dont have the same value If I have a table that looks like this

CREATE TABLE foo (
   id INT NOT NULL AUTO_INCREMENT,
   aa INT NOT NULL,
   bb INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (aa, bb),
   CONSTRAINT aa_ref FOREIGN KEY (aa) REFERENCES bar (id),
   CONSTRAINT bb_ref FOREIGN KEY (bb) REFERENCES bar (id)
)

Is there a way to make sure that aa != bb besides using application level logic or forcing a trigger to fail on BEFORE INSERT?

No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint:

ALTER TABLE foo
  ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
    CHECK (aa <> bb) ;

I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal values and simply ignore the rows by accessing the table always through a view:

CREATE VIEW foo_correct AS
SELECT id, aa, bb
FROM foo
WHERE aa <> bb ;

Alternatively, you can restrict Insert and Update operations through (stored) procedures that take care of the constraint and not allow data to be inserted (or changed) that do not satisfy it.

The both of columns in question reference to the same Bar table.
Could you split the Bar table into two so they contain id with the different sets of values?


How to store very small values (E-314) in SQL Server database? I had created table which hold small floating values using below query

create table temp_data (val float)

But when it gives warning message for very small values (e-314)

insert into temp_pce_rank_data values(2.466987305926227e-314)

Data gets inserted with value '0' warning message is: The floating point value '2.466987305926227e-314' is out of the range of computer representation (8 bytes).

How I can store & retirve very small values (E-314) in SQL Server database?
Thanks

None of the provided numeric types (float, decimal) are able to store so small numbers. The example you have, 2.466987305926227e-314, is smaller than the smallest normal double-precision float number.
I see two options, if you want to store such numbers:

Use a custom solution with two columns, one for the mantissa and another for the exponent. The disadvantage will be that you'll have to do all the math operators and functions with complex expressions (even for addition).

Use a custom CLR User-Defined Type. From that page:

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.
Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:
Date, time, currency, and extended numeric types
Geospatial applications
Encoded or encrypted data

Float has a range of - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 with a maximum data size of 8 bytes. If you really need double precision you should look at Oracle which float datatype can be 1 to 22 bytes.

Check this: https://stackoverflow.com/questions/16900314/what-is-the-best-way-to-recognize-and-convert-these-minimum-double-values-into


Oracle 10g: query takes long time and adding index does not help I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process.
What I do is:

Get "explain plan" for the query
I noticed a "table scan" in the "explain plan" graph
I added an index for the specific column
I refreshed statistics for this table

I now get "explain plan" but the figures are the same and no execution optimization.

Any ideas?

The optimizer may still look at the table scan to be a good idea.  To insure the optimizer uses the index, have you tried adding a hint to your query to insure the optimizer uses the index?  And echoing the earlier response, more information to work with would be nice.


How to validate mongodb backup and restore I am using mongodump to create a backup of the db based on some query params and mongorestore to restore the same in another mongodb in a diff environment.

How can I validate whether the dump created fetched all the records and also whether the restore of dump to the target db is done properly?

You should try this:

https://studio3t.com/

You can use "data synchronization" And if I remember correctly, you can validate data between 2 DB.

You can use it non-commercial use for free. Just download, and at the first start you can select between commercial and non-commercial use.

Also this: https://robomongo.org/


Why do we have Top N Sort in this example? In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?
use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

It's reproducible in all versions starting from 2008 R2 (I just have no server with earlier versions to test)

It's a somewhat annoying limitation of the current optimizer.

I wrote about this a little as part of The Eager Index Spool and The Optimizer:

  Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

Because you order by [v], while your primary key is on [id].

BTW: the query could become slightly faster, when you use SUM(a.v) + ISNULL(SUM(b.v), NULL) instead of sum(a.v + b.v).

On the other hand the result could vary a little bit in this case, since you use OUTER APPLY (similar to a LEFT JOIN), so when there is no record found for the "table" [b], it would ignore the a.v in the original query too (since a.v + NULL is NULL) while a.v it would be in the total sum of my suggested version (it's your data, so you have to decide, if you remain on the sum(a.v + b.v) it would be a good target for a comment saying, that this is the intended behavior :-).


Compilation error on stored procedure using PL/SQL in Oracle? create or replace procedure updateStudentResult(id int,
m1 in int,
m2 in int,
m3 in int,
tot out int,
avg out float,
grd out char)
as

begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
avg:=(m1+m2+m3)/3;

if avg >= 85 then
grd:='A';

elsif (avg >= 65) then
grd:='B';

elsif (avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=avg,
grade=grd
where sid=id;

end;
/

  PLS-00103: encountered the symbol '=' when expecting one of the following: (

  these are the line where the error occurs

  if avg >= 85 then grd:='A';

  elsif (avg >= 65) then grd:='B';

  elsif (avg >= 50) then grd:='C';

@kevinsky is correct, you cannot use avg as a variable name in PL/SQL, however that is not the only issue. You also cannot assign values to IN variables like m1, m2, and m3. My best guess is that you meant those to be local variables seeing as the first thing you do with then is SELECT INTO them.

test table:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer
, average number
, grade char(1)
);

your procedure errors:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :----------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   17 |        8 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        2 |   20 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        3 |   23 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        4 |   32 |       12 | PLS-00103: Encountered the symbol "," when expecting one of the following:

   (
 | ERROR     |            103

your procedure errors after fixing use of avg keyword:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :-------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   12 |       34 | PLS-00403: expression 'M1' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        2 |   12 |       37 | PLS-00403: expression 'M2' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        3 |   12 |       40 | PLS-00403: expression 'M3' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        4 |   12 |       43 | PL/SQL: ORA-00904: : invalid identifier                                                 | ERROR     |              0
UPDATESTUDENTRESULT | PROCEDURE |        5 |   12 |        1 | PL/SQL: SQL Statement ignored                                                           | ERROR     |              0

fixed procedure:

create or replace procedure updateStudentResult(id int,
tot out int,
v_avg out float,
grd out char)
as
  m1 int;
  m2 int;
  m3 int;
begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
v_avg:=(m1+m2+m3)/3;

if v_avg >= 85 then
grd:='A';

elsif (v_avg >= 65) then
grd:='B';

elsif (v_avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=v_avg,
grade=grd
where sid=id;

end;
/

with no errors!:

select count(*) from user_errors;

| COUNT(*) |
| -------: |
|        0 |

dbfiddle here

If the grading rules are set in stone, you may wish to use virtual columns instead of updating them at all:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer as (marks1+marks2+marks3)
, average number as ((marks1+marks2+marks3)/3)
, grade char(1) as (case when (marks1+marks2+marks3)/3>=85 then 'A'
                         when (marks1+marks2+marks3)/3>=65 then 'B'
                         when (marks1+marks2+marks3)/3>=50 then 'C'
                         else 'D' end)
);

insert into students (sid,marks1,marks2,marks3) values (1,55,66,77);

select * from students;

SID | MARKS1 | MARKS2 | MARKS3 | TOTAL | AVERAGE | GRADE
--: | -----: | -----: | -----: | ----: | ------: | :----
  1 |     55 |     66 |     77 |   198 |      66 | B

dbfiddle here

that's very simple, you have to insert a colon ( : ) before the equal sign ( = ) to construct a valid assignment operator ( := ).


Granting a user a permission to restore a database In a database I created a user with the following roles:

Server Roles

public

Database roles

db_backupoperator
db_ddladmin
db_datareader
db_datawriter

The problem is I don't want to change the user's roles, but I do want grant the permission to restore the database.

Is there a workaround to only grant the permission to restore but not to drop nor alter the database?

Background info:

The company I work for created an app and they want the users of that app to have the permission to backup and restore the database, but not to create another database or drop the existing one. And, they don't want to have another user who has the role of db_owner, which would allow the customer to manipulate things as they please.

The price for the app is based on the numbers of employees a client has, so while installing the app on the client side they use a file to create a database where they specify all the details (including the number of employees), but they fear that someone might change the file and add the database multiple times, so they want to secure the instance by not granting any user the right to do anything. It's complicated but it is what they want.

Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ability to restore, by hard-coding the RESTORE statement, you are essentially granting permission to only restore this particular database.

Detailed instructions, explanation, and walk-through demo can be found here:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

But to simplify, here are the basic steps:

(we will assume that the stored procedure will be created in the [master] DB, which means we don't need to copy the certificate to another DB. the stored procedure could just as easily be created in a utility database, in which case you just need to copy the certificate to [master] as shown in the instructions in the linked blog post)

Create a stored procedure. This can be as simple as just the RESTORE statement for a specific DB, or it can accept an input paramater for @DBName sysname and construct Dynamic SQL using that parameter value, this allowing for restore of any particular DB, or maybe a @DbToRestore TINYINT parameter that is used in a CASE statement allowing the user to select from a limited set of pre-defined database names to restore.
Create a certificate specifying a password (do not rely on the Database Master Key / DMK). Do not give the end-user / customers this password (including a script containing the password).
Sign the stored procedure (i.e. the "module") using that certificate and its password.
Create a login (not user) from the certificate.
Add the login to the dbcreator fixed server role.
If the end-user / customer does not already have a user in [master] for their login: create a user in [master] for the customer's login.
Grant the customer's user EXECUTE permission on the stored procedure.

For added security: it might be a good idea to prevent them from signing another module, or re-signing this one, if they somehow manage to get the password. This is not always necessary, but is probably a good idea here since you distribute the system to the customer.

Backup the certificate to a file
Remove the private key from the certificate

Without the private key, the certificate can only validate that a module was signed with it, but it can no longer sign anything. Since signing a module applies the permissions of the associated certificate-based login (i.e. the login that was added to dbcreator), this prevents the customer from applying those permissions to their own modules, if they ever managed to create any. ALSO, if the customer manages to alter this stored procedure to do something that you are trying to prevent, the signature is dropped, meaning that the module no longer has the elevated permissions (even if they put the stored procedure back to the original code).

Please also take a look at the following answer of mine, also here on DBA.StackExchange, to a similar question:

Execute Permissions for a Store Procedure that creates databases

An easy option would be to create a SSIS package that will do the restore, using a Proxy that uses an account with the appropriate permission.
Then all you need to do is give the permission to the user to run that job.


Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters I'm having two Azure Databases Lets say DB1(primary) and DB2(secondary).
I've Created two External Data sources for both the DB1 so that they can talk to each other

External Data Source for DB1 is External_DB2
External Data Source for DB2 is External_DB1

I'm having a Sp in my DB2.Lets say the sp is sp_DB2(@param1)
Like that another sp in my DB1.Say  sp_DB1(@param1,@outparam)
(Ps:@outparam is a output parameter)

My sp_DB2 is the Main Sp (which calls all other Sp i.e, sp_DB1).

Inside my sp_DB2 i have the code like

EXEC SP_EXECUTE_REMOTE
N'External_DB1',
N' DECLARE @param1 nvarchar(max),@outparam nvarchar(max)
EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
@param1 ,@outparam OUTPUT
SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Since sp_Execute_remote doesn't support the output param.I done like the above.But here the thing i want to bind the @param1 value from the Original Sp (sp_DB2).

If i tried like this it works,

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)=''Testing'',@outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue'

Where i hardcoded the Value of @Param1 as Testing in the Code.

But actually the value of @Param1 should come from the original Sp which is sp_DB2

When i call the Original Sp like sp_DB2(Testing) It should bind with the @Param1 for the other Sp which is sp_DB1

By doing this we can reduce the Hardcoded value of Testing inside the Sp

Pls somebody help me to solve this

Thanks in Advance !
Jay

SP_EXECUTE_REMOTE in Azure works very similar to native sp_executesql. So you should be able to bind the params & values. But I believe there is a limitation that you can't get a output param value from it. Try this and see if it works for your case. Thanks.

EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE @param1 nvarchar(max)= @param1_from_sp_DB2, @outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue',
    N'@param1_from_sp_DB2 nvarchar(max)',
    @param1 --Since this is the input param name from sp_DB2

please try this:

 Create PROCEDURE prLocalProcCallingRemoteProcWithOutput
        @LocalParam varchar(100)
    AS
    BEGIN
    SET NOCOUNT ON;

    EXEC SP_EXECUTE_REMOTE
    N'External_DB1',
    N' DECLARE  @outparam nvarchar(max)
    EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1] @param1,@outparam OUTPUT'',
    N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'',
    @param1 ,@outparam OUTPUT
    SELECT @param1 AS Paramvalue,@outparam AS outputvalue',
     @params = N'@param1 nvarchar(100)',
    @param1 = @LocalParam

    END
    GO


change master to cause 1364 ip addr|grep inet
inet 192.168.8.47/24 brd 192.168.8.255 scope global ec1
inet 192.168.8.45/24 brd 192.168.8.255 scope global secondary ec1:1

I got like this

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 192.168.8.47
                  Master_User: repl
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000126
          Read_Master_Log_Pos: 82811044
               Relay_Log_File: c60-relay-bin.000002
                Relay_Log_Pos: 426371
        Relay_Master_Log_File: mysql-bin.000125

the 8.47 ,will be remove on the master machine ,and then I do

on slave
stop slave ;
change master to master_host='192.168.8.45';
start slave ;

and then I got error like

Last_Error: Error 'Field 'xxx' doesn't have a default value' on query.

what the database do ? when change master master_host only?

how to resolve this problem ,and the right way to change master (change master to master_host,user,root..etc)?

on the slave

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------+
| @@sql_mode                                                                         |
+------------------------------------------------------------------------------------+
| IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

try with SET SQL_MODE = ''; or add this STRICT_TRANS_TABLES with existing data  modes


Postgres slave max_connections = master's max_connections? I am using binary replication for Postgres.

Is it true that the max_connections has to be the SAME for both the master and slave in postgresql.conf?

Can they be different?

The documentation has something to say about it [emphasis mine - ed.]:

  When running a standby server, you must set this parameter to the same
  or higher value than on the master server. Otherwise, queries will not
  be allowed in the standby server.

There are a number of reasons to try to keep your hardware and configuration as close to identical as possible.  So I think a strict reading of your question is no.  But there are good reasons to keep it the same.

The big reasons have to do with two things: base backups and fail over.  If you are taking base backups from the slave then the base backup also includes the configuration.  If you later restore it on the master, then you have configuration changes taking place solely because you restored a backup.  In a disaster recovery situation, the last thing you want is to be trying to track down "what changed when we restored the backup?!"

The second thing is that this applies in more or less the same way when failing over.  Because failing back is still a bit of a pain, you need to be assuming your slave can handle the master's load for a bit of time while you set things up.  Keeping the configuration the same is a good way to avoid unpleasant surprises there.

It a general convention to follow as the master and slave always synchronized themselves.. but you can change it depending your kernel buffer size and shared_buffers size you are allocating for each transaction in postgresql.conf file. so don't worry you can change the number of connection but for that you can get some more error.


Like predicate to match a whole word only I have a SQLite database with a table named minecraft.

+----+----------------------+
| id |         name         |
+----+----------------------+
|  1 | Pocket Mine MP       |
|  2 | Open Computers       |
|  3 | hubot minecraft skin |
|  4 | Terasology           |
|  5 | msm                  |
+----+----------------------+

I need to find all the records which have 'e' and 'o' in their 'name' field. Here is my Select query:

select * from minecraft where name like '%e%o%'

Here is the result of the above query:

+----+----------------+
| id |      name      |
+----+----------------+
|  2 | Open Computers |
|  4 | Terasology     |
+----+----------------+

The problem is that the Like predicate matches the entire value, not words. The row with id = 2 should not be matched, because all the criteria didn't happen in a single word ('e' is found in the first word and 'o' in the other word): Open Computers.

How should I change my Select query so it matches the only row with 'e' and 'o' in a single word?

+----+------------+
| id |    name    |
+----+------------+
|  4 | Terasology |
+----+------------+

Thanks

Your best bet is probably to use REGEXP, which needs some special installation prior to being usable.

In that case, you'd use:

WHERE
    name REGEXP 'e\w*o'

This regular expression means: (a literal 'e') followed by (zero or more word characters) followed by (a literal 'o').

This approach won't shy away from cases like 'This hello will work'

References:

How do I use regex in a SQLite query?
Online Regular Expression tester - Regex101.com

Alternative:

If you can't install the required packages to have REGEXP available, you can probably use a user-defined function to perform the "word-by-word" LIKE check. Let's imagine it's called check_words_like(sentence, like_predicate), I'd use in the following fashion:

SELECT
    whatever
FROM
    (SELECT
        name, whatever
    FROM
        tbl
    WHERE
        name like '%e%o%'
    ) AS q1
WHERE
    check_words_like(q1.name, '%e%o%') ;

This still requires some C programming and interfacing, and might be as complicated as getting the right REGEXP package.

The solution to your query will be just one simple query,

select id, name from minecraft
where name like '%e%o%' and instr(trim(name),' ')=0

Thats the shortest answer that works in sqlite.


Postgres incomplete startup packet error I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server
* Error: could not exec /usr/lib/postgresql/9.3/bin/pg_ctl /usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main -l /var/log/postgresql/postgresql-9.3-main.log -s -o  -c config_file="/etc/postgresql/9.3/main/postgresql.conf" : [fail]

So I checked the log file with this output:

 2015-01-05 21:50:05 EST LOG:  database system was shut down at 2015-01-05 21:50:03 EST
 2015-01-05 21:50:05 EST LOG:  database system is ready to accept connections
 2015-01-05 21:50:05 EST LOG:  autovacuum launcher started
 2015-01-05 21:50:06 EST LOG:  incomplete startup packet
 2015-01-05 21:51:22 EST ERROR:  syntax error at or near "exit" at character 1
 2015-01-05 21:51:22 EST STATEMENT:  exit;

"Incomplete startup packet" seems to be the culprit but  I am having trouble finding info out there on what might be going on, so I thought I would ask. Thanks in advance.

There are 3 different items in this question:

Incomplete startup packet occuring at server start is inconsequential, you may ignore it. Read Incomplete startup packet help needed (in pgsql-general mailing-list) for more.
syntax error at or near "exit" at character 1 means that a client issued exit as if it was an SQL statement.
The could not exec error when issuing service postgresql restart looks like a serious installation problem but it's contradicted by the log entry database system is ready to accept connections meaning that the server started up just fine.

Maybe your iptables are dropping packages on port 5432.
Consider adding the following rule to your iptables:

# sudo iptables -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT


Optimal drive configuration for SQL Server 2008R2 I have a fairly busy database server running SQL Server 2008 R2 that has the following setup:

SATA RAID 1 (2 Drives)  - OS / Programs
SAS  RAID 10 (4 Drives) - Sql Database Files (data and logs)
SAS  RAID 1 (2 Drives)  - TempDB (data and logs)

Assuming I can't add additional drives into this server, have I made the best use of the configuration I have available? Or should I consider another scheme here where logs are isolated from the data files, for example?

Update:

For those that requested further hardware details:

The SATA drives (used for the OS / Program partition) are: WD 7200 RPM 3 Gb/s 3.5 Inch SATA
The SAS drives used in the other arrays are: Seagate 15K RPM 6 Gb/s 3.5 inch SAS
The RAID controller used is an: LSI 9260-8i SAS/SATA 6 Gb 8 port

Update 2:

Based upon the feedback I've received, it looks like I have the following viable options to choose from - I will award the bounty to someone that can tell me which is likely to be the best in the environment that I've outlined:

Leave everything as is - I probably won't do much better
Move my 2 SAS RAID 1 drives into my existing RAID 10 array to have it composed
of 6 disks in total
Move my log files onto the SAS RAID 1 and/or
relocate TempDB (data or logs) back to the RAID 10

Variants of this question come up semi-regularly:

SQL Server build
Help me choose a RAID level combination for a SQL Server 2008 instance
How should I configure these disks on a SQL Server for a BI configuration?

There are also occasional bun fights about the data/log separation "best practice".

Placing Transaction Log on Separate Volume [solid state]?
SQL Server - Separating data, log, and TempDB files on a SAN

Without more detailed analysis of what this server is doing, the same advice applies as given previously.

RAID 1 for OS
RAID 10 (6 disk) for data/logs/tempdb

There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.

One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.

Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.

Depending on what you are using the DB for (OLTP vs warehousing) your config looks like a good general config.  If you had more disks, you would have more options.

You could get better performance if you switched the disk for your TempDB to RAID 0 (stripe).  It increases your risk for failure, but since TempDB only buffers data, you can't experience data loss. So most people consider that a reasonable trade-off.  Just keep a spare around (or a hot-spare).

One thing you didn't mention, but could try (if you haven't already): Microsoft recommends splitting your TempDB up over several files (one per CPU).  Of course, it is best if they are on separate disks, but simply having separate files helps.  http://msdn.microsoft.com/en-us/library/ms175527(v=SQL.105).aspx


Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014 I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size.
Now I am stuck using Diskpart to set the perfect offset. As far as I understand I have 63 hidden sectors and to align it with my formatting I can use an offset of 1024k , did I get the right?
Anyway when I list my partition afterwards I get this result:

Using command line wmic partition get BlockSize, StartingOffset, Name, Index

I get a starting offset of 135266304

Some websites/blogs recommend 32K other do recommend 1K for 2012, whom can I trust? Currently I have issues understanding it correctly. May the force (you) enlighten me?

For what is worth, I only use the 64K allocation unit size when in production or UAT environment and if I have a cluster with a SAN in which I have a LUN for the Data files, another LUN for the LOG files and another LUN for the TEMPDB files, at least.

Other consideration is that if your environment only have one disk and this is the OS disk, leave the allocation unit size as is. If you are trying to setup SQL Server 2014 on a different disk from your OS disk, perhaps in that disk you will get better performance with the 64K allocation unit size, but do consider that a best practice, both for performance and maintenance, states that you separate your data files, log files and TEMPDB files, at best, in different disks. This from a suggestion in this blog post from the SQL Server product group: http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/19/disk-and-file-layout-for-sql-server.aspx

Check out the SQL Server Setup Checklist from Brent Ozar.  Section 4.1 covers drive formatting and block sizes.

SQL Server Setup Checklist from BrentOzar.com


Unable to attach database to Amazon RDS using SQL Server Management Studio 2016 I'm attempting to attach an mdf file to an instance of SQL Server running on Amazon's RDS service.  Using Microsoft SQL Server Management Studio 2016, I follow the following steps:

After connecting to the amazon instance, I right click on the Databases directory and select Attach.
Up comes the Attach Databases dialogue
I click the Add button to select the local .mdf
I get the following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I've seen this error message on several forums but always when trying to modify the database (at this point I believe I'm only bringing up a file browser to locate the mdf).  I've also tried running it as admin but no luck.  I'm quite new to SQL Server and am much more at home in a Linux environment, so I wouldn't rule out something obvious.  Any help would be appreciated.

With Amazon RDS, you don't get direct access to the underlying filesystem.

If you have an existing database that you want to migrate into RDS SQL Server, rather than using the MDF/LDF files, you'll need to do a restore instead.

Amazon's instructions to restore a database are pretty good, but since you're asking this question, I'm guessing I probably need to fill you in on a background concept. Remember how I said you don't get access to the filesystem? Well, you can get access to OTHER file systems, kinda like how mapped drives work in Windows. S3 is Amazon's simple storage service where you can create buckets and put files in 'em. You're going to need to:

Create an S3 bucket
Put your database backup (not MDF/LDF) in the bucket
Give your RDS instance permission to read from that S3 bucket (that's where Amazon's instructions come in)
Use Amazon's custom stored procedure rds_restore_database to restore the backup

I had the same issue and this solution worked for me in SQL Server 2013 and 2017

start
run
type (services.msc) in run open it
go to SQL Server (MSSQLSERVER)
click on proprieties Select Log on
Select "Local System Account" (the default was some obtuse Windows System account)
stop SQL Server (MSSQLSERVER) then start it


What options in /etc/mysql/my.cnf are applicable to percona-server-client-5.6? I installed percona-server-client-5.6 on Ubuntu; there is no mysql server installed on that same machine.

In /etc/mysql/, the only file is my.cnf. That file contains many sections of options and I am not sure which sections are applicable to the machine with only the client package installed.

I assume the [client] section of course, but why to the other sections exist at all? Will changing their values affect the mysql server the client connects to?

my.cnf:

[client]
...

[mysqld_safe]
...

[mysqld]
...

[mysqldump]
...

[mysql]
...

[isamchk]
...

Those other sections are there because they are included in the template cnf file from the package provider (Percona).

You can create a user specific cnf file in your home dir that looks like the one below and they will get used when you run the mysql command:

[client]
user=$myusername
password=$mypassword

n.b. if you're going to store your credentials in a cnf file make sure you  set the permissions to 400.

mysql install my.cnf as template with all sections and commented help information

for start work You generally will need only [client] section

for connect use command line like

user$ mysql -h 192.168.10.111 -u root -p


Why is a 'Select distinct limit 100' using tmp tables? I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.
The following query should check row by row, save it to memory if it's unique from the other's already selected or skip if it is a repeat. Once results 100 are in memory it should terminate the query and output the result. Instead it goes through every single result, loads them into a temporary table on the hard disk sorts them and pulls the first hundred sorted ascending. It should take a fraction of a second, not several hours.
Query
SELECT DISTINCT `some field` FROM `myTable` LIMIT 100

EXPLAIN SELECT DISTINCT `some field` FROM `myTable` LIMIT 100:
id|select_type|table   |type|possible_keys|key|key_len|ref|rows      |Extra
1 |SIMPLE     |myTable | ALL|             |   |       |   |223193820 |Using temporary

There's no reason for it to sort anything aside from that's how it's programmed. It only needs to return 100 distinct values which I could do much faster manually parsing the raw text.
If it only needs 100 distinct values it doesn't have to check 220 million rows to make sure that a value isn't duplicated in the 100 rows that it is supposed to return.

There are multiple ways that SELECT DISTINCT col FROM ... LIMIT 100 can be performed:

Case 1:  Create a temp table with all rows of col, sort it, then peel off the first 100 (de-dupping as it goes).

Case 2:  Build a hash in memory, read col putting it into the hash, stop after 100.  (Fast.)

Case 3 (if there is INDEX(col)):  Leapfrog through the index.  This effectively takes 100 BTree dives into the index.  (Fast, even if few distinct values.)

Which will it do?  It depends on the version, the data, the existence of an index, etc.  Note that 5.1 is antique; you should plan to upgrade to 5.5, then 5.6, then 5.7.  (8.0 is also available, but not "GA".)  The Optimizer has changed a couple of times in this area, so I cannot say what your version will do.

If you are going to do this for all columns, consider, instead,

SELECT GROUP_CONCAT(DISTINCT col1),
       GROUP_CONCAT(DISTINCT col2),
       ...
    FROM TABLE;

(No LIMIT.)  This won't give you exactly 100 of each, and it will probably give you only the alphabetically/numerically first several (1024 bytes' worth) of each list, but maybe that is good enough.  It is probably faster than column-by-column.

See also ANALYSE() (spelled that way).

The answer is that mysql 5.1 performs DISTINCT as group by and order by actions before truncating the rest of the results. It's designers chose to make it write the full data set of the column to a temp table and then sort them even though you probably don't need it to.

In my opinion it makes DISTINCT fairly worthless because you can just as easily use group by.


Get list of DB2 databases and tables through linked server Is there a way to get a list of the databases on a DB2 server through a linked server query?  I tried using an openquery to run "list database directory" but that didn't work.

I am attempting to do this because I do not have direct access to the DB2 server.

Please note that I already have the linked server set up.

You cannot get a list of databases via ODBC; ODBC requires a connection to a specific database, and a database does not know anything about the outside world, specifically, if there are any other databases. Depending on a particular DB2 platform, even the concept of "other databases" may be nonexistent.

list database directory is a DB2 CLP command, not an SQL statement, and as such is not understood by the SQL engine.

As for the list of tables in the connected database, you can query the catalog view SYSCAT.TABLES (provided we're talking about DB2 for LUW; refer to the appropriate product manual if not).

This worked for me.  I needed to get a list of tables so I could export the names.  I am using a SQL Management Studio and had the DBA setup a linked server to AS/400.  I replaced {linked_server} with the name of my linked server

select * from openquery({linked_server}, 'select * from qsys2.tables where table_type = ''BASE TABLE''')


How to determine the optimal sort_buffer_size? I read from a sample configuration file which says the following:

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.

I have a couple of queries which use filesort. How do I determine what is the size of the buffer I need for queries to run smoothly without hitting the disk?

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says:

  Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Please keep in mind one thing about sort_buffer_size

  If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing

While raising sort_buffer_size can help queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

Question remains: How do you check the Sort_merge_passes ???

Use this code, to check how many Sort_merge_passes happened in the last 5 minutes. It also computes the Sort_merge_passes per hour.

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

If you find the Sort_merge_passes and the rate too high, then feel free to increase sort_buffer_size. Suppose you want to raise to 4M. You would run this:

mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;

You would then add this to my.cnf

[mysqld]
sort_buffer_size = 4M

You would run the code periodically to check for other times Sort_merge_passes spikes.

"mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;"
its a bad way to put in 4m the sort buffer size,  thats makes sort buffer size have 4GB
use

"mysql> SET GLOBAL sort_buffer_size = 1024 * 4;"

If i'm been your, i don't try to change short buffer size, that is a good way to make crash your server, and send to trash performance.
It's better try to make better querys.