MS SQL -Date and Time Style conversion problem I need to convert Date and Time Style from USA format to German and than cast that into int data type.
So for variable in case below this works fine if I want to display it in Standard 112 (ISO)

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original,
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS int;

But when I put 104 which suppose to gives me error:

  Conversion failed when converting the varchar value '23.10.2015' to
  data type int.

It looks like conversion is actually working but casting into int is not.

The 104 format has . in it and thats why it is failing vs the 112 has not dots so you can cast that as int. See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original,
CONVERT(VARCHAR(8), @LoopDate, 104) as GermanDate_WithDot, -- see the dot
 cast(replace(CONVERT(VARCHAR(8), @LoopDate, 104),'.','') as int)  as ProperlyCastedGermanDate_WithoutDot,
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS ISOCasted_Date_asInt  ;

Note: Casting date to int for some later math is not a good idea.


Can't finish my ER diagram I am trying to create a database for a small book library. This is not a professional project, just an exercise of my own. This is my first time to try this ( I am self-taught ) and I am encountering a problem. Before I continue I will provide my version of the ER diagram below ( I made it in Paint, using the following resource for notation ):

My biggest problem is to solve the fact that book can have the same title but have several editions or be a part of a set. An example would be Lord of the Rings trilogy, or Programming Windows, 5th edition, or Encyclopaedia of chess openings, volume D.

I don't know how to incorporate this into my ER diagram so that I can avoid data redundancy.

Therefore I ask these questions:

How should I address the fact that book can have the same title but have several editions or be a part of a set, in my ER diagram?
Since I am inexperienced, can you point out mistakes in my ER diagram, if there are any, and suggest an improvement?

Since this is my very first post here, please leave a comment if anything needs to be changed ( if it doesn't fit this site's question format ) or if you need further info.

Thank you.

I would say that an Edition is an entity that is related to a Book.

You then have a one-to-many relationship between Books and Editions.  (One book can have many editions.)  and a many-to-one relationship between Editions and Publishers  (many editions are published by one publisher).

When I was building actual databases, back in the day, I always had access to one or more "subject matter experts",  people who had become familiar with the data through constant use.  Most subject matter experts were neither computer experts nor database experts.  Your situation is somewhat different.

I would try to find some kind of on line subject matter expert, like the Library of Congress.  They might have some kind of model of the publishing industry that would provide answers to this and other questions you have regarding how the field of publishing really works.  And this model might be in the public domain.

PS:  Congratulations on using the ER model as an analysis tool,  and not diving into database design prematurely.


Dropped table not showing up in recycle bin I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it .
What's the deal ?
[update]:
i had tried
   SHOW PARAMETER RECYCLEBIN;

Its VALUE column shows it  as ON . So fine here !
Then i droppped an existing table but still
the command
    Select object_name from recyclebin;

and any such retrieval of columns is showing NO ROWS SELECTED !!
Also using flashback command  confirms that recyclebin is not having any such dropped tables !!
So where are dropped tables going ?
And one more thing :
This time i tried using
 ALTER SYSTEM SET RECYCLEBIN=ON;

Instead of Alter session    and sqlplus throws an error :
Specified initialization parameter is not modifiable with this option
So, is this because  that i am working as a SYSTEM ??
i have not created any other USER as of now .

It's likely that recyclebin isn't turned on. Check in your init.ora file to ensure it is. You can check your parameter:

select *
from   V$PARAMETER
where  NAME = 'recyclebin';

And turn it on at session level...

ALTER SESSION SET RECYCLEBIN=ON;

...and then try a drop.

Also, check both USER_RECYCLEBIN and DBA_RECYCLEBIN for entries.

EDIT: I see you've updated to say you are logged in as the SYSTEM user. For some reason, flashback/recyclebin does NOT work for SYS. I can't find a good reason why but it just doesn't. Create a new user and try creating and dropping a table while logged in as them. I'm sure you'll find that the recyclebin works fine then.


Does the scope for 'Alter Any Database' operate similar to 'Create Any Database'? Does the scope for 'Alter Any Database' operate similar to 'Create Any Database', as in, only those databases created by Login-A under the 'Create Any Database' umbrella can be altered, and not the other databases on the server?

Why is this so hard to test for yourself? As sa or similar:

CREATE LOGIN Jake WITH PASSWORD = N'x', CHECK_POLICY = OFF;
GO
GRANT ALTER ANY DATABASE TO Jake;
GO
CREATE DATABASE IBelongToAaron;
GO

Now log in as Jake:

CREATE DATABASE IBelongToJake;
GO
ALTER DATABASE IBelongToJake SET RECOVERY FULL; -- success!
GO
ALTER DATABASE IBelongToAaron SET RECOVERY FULL; -- success!

So no, ALTER ANY DATABASE works as advertised, which is probably why it's not called ALTER ANY DATABASE THAT I CREATED OR OWN.


Change host IP of master in mysql replication I have one master and one slave. Suppose IP of master is changed but the physical server is same. Now I want to update the new IP (of the master) at the slave server. At the slave server master_info_repository is set to TABLE. I want to resume the replication process from exactly the position where it has paused due to IP change. How to do this? mysql version of slave server is : 14.14 Distrib 5.7.30 and O/S is ubuntu 18.04.

After a long internet surfing I got a thing. MASTER_LOG_FILE will take Relay_Master_Log_File and MASTER_LOG_POS will take Exec_Master_Log_Pos as their respective value in CHANGE MASTER TO statement. In the Exec_Master_Log_Pos section of mysql docs it is clearly stated. I have found another article which says the same thing :

When you’re using CHANGE MASTER TO to set start position for the slave
you’re specifying position for SQL thread and so you should use
Relay_Master_Log_File:Exec_Master_Log_Pos. Otherwise you’re going to
ruin your replication.

It does not help SHOW MASTER STATUS has Master_Log_File value and
CHANGE MASTER TO accepts Master_Log_File parameter, while you actually
want to use Relay_Master_Log_File instead. This naming was left from
pre MySQL 4.0 era and it still causes confusion so many years later.

Conclusion :

STOP SLAVE;
SHOW SLAVE STATUS \G
note down Relay_Master_Log_File value (Example : mysql-bin.000007) and Exec_Master_Log_Pos value (Example : 775)
CHANGE MASTER TO MASTER_HOST='<New IP of Master>', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=775;
START SLAVE;


ALTER DATABASE script preventing restore of MySQL database in another server While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines.

/*!50003 DROP FUNCTION IF EXISTS `f_my_function` */;
ALTER DATABASE `my_database` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;

The database character set is set to utf8.
Presence of this line is preventing the restore of the database in another system.

What is the fault?

The problem rests with the character set of INFORMATION_SCHEMA.ROUTINES
Run SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES\G and you should see
mysql> show create table information_schema.routines\G
*************************** 1. row ***************************
       Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
  `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` int(21) DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
  `COLLATION_NAME` varchar(64) DEFAULT NULL,
  `DTD_IDENTIFIER` longtext,
  `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) DEFAULT NULL,
  `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(64) DEFAULT NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
  `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `ROUTINE_COMMENT` longtext NOT NULL,
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

In order to load the routines into your new system, you can filter it using grep
EXAMPLE
Suppose the script with the ALTER DATABASE is mydata.sql. Just do this
grep -v "^ALTER DATABASE" mydata.sql | mysql -uroot -p...

This will bypass running the ALTER DATABASE.
Why is ALTER DATABASE included ? According to the MySQL Documentation

mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.

The database's character set and collation must have been CHARACTER SET latin1 COLLATE latin1_swedish_ci when you dumped from it. My guess is that the dump originated from a Windows version of MySQL .


MySQL Error: “Table storage engine for `proc` doesn't have this option (ERROR 1031)” I'm running into a strange problem with MySQL. Searched everywhere for a fix but cannot find anything. Running Ubuntu 14.04.

I have a database running on Percona MySQL 5.7.18-16. I want to update this to Percona Server 5.7.19-17. I run a script that performs a mysqldump through Ruby on Rails in a Rake task.

When I use the updated Percona and get to the mysqldump stage, I get the error as stated in the title. The table that it refers to is the internal mysql.proc table. I have no idea why this is occurring.

I noticed that if I run mysql_upgrade --force before the dump, it works (although mysql_upgrade runs into the same error but seems to ignore it). I do however want to get to the root cause so that I do not have to run mysql_upgrade. I ran a diff on my database .sql files before and after mysql_upgrade, and there was no change.

Does anybody have any ideas as to how to get to the root of this problem and fix it? Any and all suggestions will be greatly appreciated!

Thank you!

The error you got is often seen when you try to use a table option that is incompatible with the storage engine used by the table you're working on.

For example,

CREATE TABLE mytable (...) ROW_FORMAT=FIXED;

This table option was deprecated in MySQL 5.7. It will raise an error if innodb_strict_mode is enabled.

You should always run mysql_upgrade after installing an upgrade of MySQL (it says so in https://dev.mysql.com/doc/refman/5.7/en/upgrading.html). Sometimes a new version introduces a change to system tables, and you should apply those changes after you update the software.

I'm guessing you have not run mysql_upgrade in a while, so the dump produced from your previous installation may have still used system tables in the format of some earlier version of MySQL.

So it wouldn't hurt to run mysql_upgrade before dumping the system tables before you upgrade.


db connection uses an anonymous logon instead of the domain user I have this PHP code running under IIS:

$serverName = "someServer\someServer";
$connectionInfo = array( "Database"=>"SOME_DB");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.
";
}else{
     echo "Connection could not be established.
";
     die( print_r( sqlsrv_errors(), true));
}

When I try to visit the page, I get the error:

  [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Which is confusing, as the documentation here says "The connection will be attempted using Windows Authentication".

The flow of this is:

User using x Windows account on workstation1 visits this website hosted on server1
server1 executes above PHP code and tries to retrieve data from server2 using y Windows account (y is defined in the IIS settings, under Application Pools->Identity)
Windows account y has access to the database

The IIS pool is running under a Windows domain user "domainadm" which has access to the SOME_DB database and the server it sits on.

Why is it trying to authenticate anonymously and how can I fix this so it runs under the user the IIS pool is running as?

In php.ini:

fastcgi.impersonate = 1;

Checking the Authentication section in IIS, the only options I have are "Anonymous Authentication" or "ASP.NET Impersonation". No Windows authentication?

Anonymous is currently enabled, which gives the error above. If I switch to the ASP.NET one I instead get this error when visiting the page:

  500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.

Reading the manual I found something relevant:

  For example IIS 7, in its default configuration, has anonymous
  authentication enabled with built-in user account IUSR used as a
  default identity. This means that in order for IIS to execute PHP
  scripts, it is necessary to grant IUSR account read permission on
  those scripts. If PHP applications need to perform write operations on
  certain files or write files into some folders then IUSR account
  should have write permission to those.

And iis.net has the following relevant information:

  This built-in account does not need a password and will be the default
  identity that is used when anonymous authentication is enabled. If you
  look in the applicationHost.config file you will see the following
  definition:

  <anonymousAuthentication enabled="true" userName="IUSR" defaultLogonDomain="" />

  This tells IIS to use the new built-in
  account for all anonymous authentication requests.

Hope it helps


conditional subquery I have the following query:

SELECT id,
   email,
   first_name as "firstName",
   last_name as "lastName",
   is_active as "isActive",
   password,
   access,
   CASE
     WHEN access < 3 THEN (
       SELECT
         CASE WHEN count(*) = 1 THEN true ELSE false END
       FROM user_rating_entity ure
       WHERE ure.user_id = u.id
         AND ure.rating_entity_id = :re_id
     )
     ELSE true
   END as "isResponsible"
FROM users u
WHERE u.id = :id

If access > 3, field "isResponsible" should be directly set to true and the subquery should not be executed.  I used explain analyze with both cases where access >= and < to 3 but I get the same output.

Why is that so?

There are three important parts to reading query plans here,

Did it run. If so,
How many times?
Was it correlated?

Sample Data

You didn't provide any sample data, so let's create some.

CREATE TABLE foo AS
SELECT x FROM generate_series(1,100) AS x;

And, now let's run a basic query with subquery, outside of the possible range of execution.

EXPLAIN ANALYZE
SELECT
  x,
  (CASE WHEN x>200 THEN (SELECT sum(x) FROM foo) END)
FROM foo;

The plan will show that the case is accompanied for, but never executed.

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.017..0.047 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (never executed)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (never executed)
 Planning time: 0.101 ms
 Execution time: 0.118 ms
(6 rows)

You can see that with (never executed) on the Aggregate line. However, if we set it to something like CASE WHEN x>20 THEN (SELECT sum(x) FROM foo you'll see a lot more

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.020..0.095 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.043..0.043 rows=1 loops=1)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (actual time=0.006..0.019 rows=100 loops=1)
 Planning time: 0.092 ms
 Execution time: 0.158 ms
(6 rows)

Here we can see that the Aggregate is looped through loops=1 time. PostgreSQL realizes that it isn't a correlated subquery and it's a just a reduces it to a literal (essentially). Now let's make sure it's correlated.

EXPLAIN ANALYZE
SELECT
  x,
  (CASE WHEN x>20 THEN (SELECT sum(f2.x)+f1.x FROM foo AS f2) END)
FROM foo AS f1;

Now you'll see this plan

 Seq Scan on foo f1  (cost=0.00..228.50 rows=100 width=4) (actual time=0.020..3.210 rows=100 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=80)
           ->  Seq Scan on foo f2  (cost=0.00..2.00 rows=100 width=4) (actual time=0.005..0.017 rows=100 loops=80)
 Planning time: 0.104 ms
 Execution time: 3.272 ms

Here the key is that the aggregate has loops=80 which itself requires loops=80 seq scans.

This is all general, but it's all I can give without your sample data, or query plans.


Query Optimizer using clustered index seek the wrong time I am supporting a SQL Server 2012 Enterprise edition with SP3. One of the tables in our ERP database has 8 million rows.  It has a clustered index and 1 non CI. For simplicity, let's say the clustered index is on column Size and the non CI is on columns Size, Weight, Color.

We have a query that goes like:

select top 1 from table_name where size=@P1 and weight=@P2 and plant=@P3

It's a Prepared object type. Historically, the query optimizer does an Index Seek on the non CI and do a key lookup on the CI.

Earlier this week, users started complaining that the database response time seems to be slower than normal.

When I look at the plan cache, this is when I found out the query optimizer has started doing a index seek on the CI instead.  You would think it should be faster but the I/O cost is over 200.  When I look at the table, there is only one distinct Size value (10) in the table. So now, SQL server has to traverse the 8 million rows to find the second and third fields in the predicate.

I did a DBCC FREEPROCCACHE and when I look back at the plan cache it went back to using the non CI which made the query fast again and users happy. I am able to reproduce the issue by clearing the cache and running the query with different parameters.

Would this be a SQL Server query optimizer bug?

Looks like query performance sometimes suffers due to parameter sniffing and the row goal implemented by your TOP 1 operator. I'll create some sample data similar to your table to illustrate what might be going on:

CREATE TABLE table_name (
size INT NOT NULL,
[weight] INT NULL,
[location] INT NULL,
INDEX CIX CLUSTERED (size)
);

-- insert around 6.5 million rows, lots of skew for weight column
INSERT INTO table_name WITH (TABLOCK)
SELECT 10
, FLOOR(1000 * SQRT(1 - (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 6500000.)))
, 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX IX ON table_name (size, [weight]);

CREATE STATISTICS table_name__weight ON table_name ([weight]) WITH FULLSCAN;

Here is an abbreviated histogram for the weight column:

╔══════════════╦════════════╦═════════╦═════════════════════╦════════════════╗
║ RANGE_HI_KEY ║ RANGE_ROWS ║ EQ_ROWS ║ DISTINCT_RANGE_ROWS ║ AVG_RANGE_ROWS ║
╠══════════════╬════════════╬═════════╬═════════════════════╬════════════════╣
║ 145          ║ 0          ║ 947     ║ 0                   ║ 1              ║
║ 172          ║ 27219      ║ 1139    ║ 26                  ║ 1046.885       ║
║ 190          ║ 20395      ║ 1261    ║ 17                  ║ 1199.706       ║
║ 207          ║ 21119      ║ 1379    ║ 16                  ║ 1319.938       ║
║ ...          ║ ...        ║ ...     ║ ...                 ║ ...            ║
║ 996          ║ 0          ║ 77686   ║ 0                   ║ 1              ║
║ 997          ║ 0          ║ 92220   ║ 0                   ║ 1              ║
║ 998          ║ 0          ║ 120274  ║ 0                   ║ 1              ║
║ 999          ║ 0          ║ 290616  ║ 0                   ║ 1              ║
╚══════════════╩════════════╩═════════╩═════════════════════╩════════════════╝

For example, thee table has 947 rows with a weight of 145 but 290616 rows with a weight of 999. Those filtered values will be used later.

You said:

  You would think it should be faster but the I/O cost is over 200.

The first important thing to realize is that not all clustered index scans or seeks are considered equal. SQL Server may report the same IO cost and CPU cost but it may scale down the total cost of the operator depending on how many rows it expects to read. Consider the following two trivial queries:

select top 1 [location]
from table_name;

select top 9999999 [location]
from table_name;

Here's the query plan:

Obviously the first query should be cheaper, but the query plans look the same. The operator cost for the first scan is 0.0032831 optimizer units and the operator cost for the second scan is 24.1525 optimizer units. Both scans have the same reported IO cost which is confusing. The physical operation for both queries may be a scan but the first scan can stop after it finds the first row. The row goal introduced by the TOP 1 reduces the subtree cost of the first query but not the second one.

You also said:

  When I look at the table, there is only one distinct Size value (10) in the table. So now, SQL server has to traverse the 8 million rows to find the second and third fields in the predicate.

Having just one distinct value for size actually favors the clustered index seek plan. The same principle described above applies when you have a filter. SQL Server may estimate based on statistics that it won't need to read all of the pages from the table to find just a single row that matches the filter. So if a filter condition is always met that means that SQL Server will have to scan less rows to find a match. For example, the following queries have different costs:

select top 1 [location]
from table_name
where size=10 and [weight]=999;

select top 1 [location]
from table_name  WITH (INDEX(0))
where size=10 and [weight]=145;

I'm using INDEX(0) to force a clustered index seek for the second query plan. Here's the query plan:

The first query has a lower cost because more rows in the table have a value of 999 for weight than 145. SQL Server expects to read back fewer rows in the scan before it finds the first matching one for the first query. The actual query plan in SQL Server 2016 shows exactly how many rows were scanned before a match was found. The first query is the best possible case because SQL Server scanned one row to find a match:

The second query is nearly the worst possible case because SQL Server scanned almost all rows to find a match:

Due to how the data was loaded we ended up with very inaccurate estimated costs. The costing model used for row goals can lead to inefficient queries if the assumptions of that model don't match how the data is distributed in the pages of the table.

Without the hint, you get the plan that you were expecting when filtering on a weight of 145:

I suspect what happened was there was no cached plan for this query. The first query that ran had very nonselective filter values for weight and plant. SQL Server calculated due to the row goal that a clustered index seek was cheaper than a seek with your nonclustered index and that plan was cached for later reuse.

So, what can you do about it? SQL Server 2016 has a query hint that can disable the row goal optimization for an individual query. This hint could have negative effects on other parts of the plan, but it will disfavor the clustered index seek that you want to avoid:

select top 1 [location]
from table_name
where size=10 and [weight]=999
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));

Since you're on SQL Server 2012 you can use trace flag 4138 as described here. If you'd like something less drastic, you could try defining a covering index on the table, you could add an ORDER BY to make the query deterministic, you could add an index hint to the query, or you can force the query plan using any of the available options in SQL Server 2012.


How to design future proof tables? I am new to database design and have started developing my first schema.

Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular use case could be that a user wants to add an additional field to their profile e.g. favourite color.

I have read a few articles that say the way to capture this use case is to include an 'other' field to the database. This field then maybe contains a json string of additional fields.

Is this method the best practice way to add additional fields to a table? It seems somewhat hacky to me. The obvious con is that the additional fields will not be as accessible when the database runs a query.

Note: I can see that there may be two different scenarios here. One where users have a personalised list of fields (in which case i think the method above will be the only way). Another where over time I realise all users would benefit from having an additional field in their profile. Comments on both scenarios would be appreciated.

Where info is personalised it should be normalised into linked tables that only join to rows where that optional column has been added.  Also if you might be adding many columns or long data it is common to have a core user table with extended info tables mapped on a one to one basis.  These methods allow for smaller indexes more specialised to each query scenario.

Generally, good normalisation and matching tables well to real world entities rather than theoretical ones gives the most adaptable and future proof design.


Using secondary id as foreign key to other table So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table.
Instead of using Customer Id as foreign key to the Configuration table, I would like to introduce a secondary id called Reference. This Reference has a value similar to GUID and would like to use this as Foreign Key to the Configuration table. The reason behind this is because I don't want to use the Id of the Customer table. We use integer as a data type for this and I don't want to expose this.
What could be the possible complications on implementing this approach?

It's not complicated to do.  You need to create the Reference ID as an alternate key in the Customer table with a unique constraint.  Then you can use Reference ID as a foreign key to the Configuration table.  I generally try to avoid this however as it can get confusing and harder to maintain the data model.


Enlighten me about INCLUDE on indexes Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES:

He gives some examples and etc. about INCLUDE, but I'm confused about the actual index he creates.

This is the query:

SELECT Id, DisplayName
FROM   Users
WHERE  Reputation > 400000;

What I would do is:

CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)

I thought we should use what's in the WHERE on the index, and use INCLUDE on those fields in the SELECT.

But Erik did this:

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

The problem here is not the filtering index. My question is why he's using DISPLAYNAME and ID in the index, and REPUTATION in the include clause?

And just for a simple test, running this query for a test in a table with millions of rows:

select COL1
      ,COL2
      ,COL3
from MyTable
    where COL2 > 4513516

SQL tells me to create this index:

CREATE NONCLUSTERED INDEX ix_nada
ON MyTable
    (
        [COL2]
    )
INCLUDE
    (
        [COL1],
        [COL3]
    )
GO

what you read and did is correct ?

Suppose, you have no index on user table,then

CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
WHERE Reputation > 400000;

This index is ok.

But this was not the point @Erik wanted to demonstrate.

According to that blog,suppose you already have any such index like,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )

And you are require to create filtered index on Reputation then just be mere mentioning it in Include will do the job and save from creating expensive index.

so he dis this,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

Filtered Indexes: Just Add Includes This is the new point about Filtered Index that he wanted to show.


MySQL not returning successful results on statement - query looks like it's hanging but is successful So we have a script(s) that runs in succession.
Randomly this task would halt/freeze and error out simply because it did not see a returned response from the MySQL server saying the statement ran fine breaking the entire procedure.
However when manually checking against the DB, the statements all ran fine it's just the script (using JBDC) aborted since it didn't see a response.

Nothing in the error logs, etc.

Is there a setting or reason why MySQL would be doing this?
This is version 5.1 .

I thought of network issues causing this but this seems to happen in 2 different environments, both through a WAN and local LAN .

any ideas?

You can try setting up a shell script that runs the SQL scripts or write a program in perl/whatever language which tries to log the errors and return codes from MySQL. Otherwise it is very difficult to troubleshoot without knowing the errors.

You could also add SHOW ERRORS; and SHOW WARNINGS; to your SQL scripts, but I suspect the problem is not with your queries.


SSAS ErrorCode="3239575554" Either the user does not have permissions to alter the 'SERVER' object I have just installed SSAS on a server called SASBIO1,
but when I try to run the script below, in order to grant Administrator rights to a specific AD group I get the error message:

<Error ErrorCode="3239575554" Description="Either the 'mycompany\MMiorelli' user does not have permission to alter the 'SASBI01' object, or the object does not exist." Source="Microsoft SQL Server 2014 Analysis Services" HelpFile="" />

This script grants Admin to BIUsersLIVE:

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object />
        <ObjectDefinition>
            <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>SASBI01</ID>
                <Name>SASBI01</Name>
            </Server>
        </ObjectDefinition>
    </Alter>
    <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <RoleID>Administrators</RoleID>
        </Object>
        <ObjectDefinition>
            <Role xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
                <ID>Administrators</ID>
                <Name>Administrators</Name>
                <Members>
                    <Member>
                        <Name>mycompany\BIUserslive</Name>
                    </Member>
                </Members>
            </Role>
        </ObjectDefinition>
    </Alter>
</Batch>

The same error happens when I try to do it from SSMS, as you can see on the picture below.

I know I am admin, I am the DBA who installed it.

What is happening?

this was really nonsense.
I right click on ssms and run as administrator, and added the permission there without a problem.


Store long number in MS access How can I store 12 character long number in MS Access 2016, if the field cannot be empty?

Try the following solution:(Is Null Or Not Like "*[!0-9]*"):


Oracle TRANSLATE Not Returning Expected Result We are trying to execute TRANSLATE on a couple hundred rows of data. We have a temporary table containing ID's to rows that need to be updated using TRANSLATE. The odd part is if we attempt to select say two rows, the command is executed properly and the information is updated. However, when we try to run it against 200 rows, it says that 200 rows were updated, but in inspecting the data, they have not been updated. Is there a limitation to the number of rows that can be updated using TRANSLATE in a single query?

We're doing something like:

UPDATE TABLE_A SET COLUMN_TO_UPDATE =
TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

Phil is right, "No, there isn't a limit", "it /should/ just work", and " this will be difficult to answer without copies of the data or another way to reproduce".  Here is a demonstration:

/*
drop table table_a;
drop table temp_table;
create table table_a as
    (select level id, to_char(level) column_to_update from dual connect by level <=200);
create table temp_table as (select level id from dual connect by level <= 200);
update table_a set column_to_update='2ñ' where column_to_update='2';
*/

SELECT * FROM table_a;

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

SELECT * FROM table_a;

You could try adding a WHERE clause to verify that TRANSLATE is actually changing rows.

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE)
AND column_to_update <> TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') ;

If this returns 200 rows then you might not be looking at the correct rows.  If it returns zero rows then the TRANSLATE function is not changing the data.


Run Multiple Remote Jobs I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job just calls a stored procedure without parameter, which is also the same across all the instances. These jobs are on a schedule. But now they want me to manually run the job for all the instance or for specified instances upon request.

What is the best practice for this? I have tried openrowset to call the remote stored procedure. But each run of the job takes couple of minutes, so if I use a loop to run all these jobs, it will run one by one and that's a long time. Ideally, it should be able to run the stored procedure on each instance without waiting for it to finish. More ideally, it should be able to run the job on each instance without waiting for it to finish, so it can leave a record in the job history on each instance.

And the stored procedure is from a third party so it can't be altered.

update:

since the 'people' ask this to be initialised from a SSRS report, use SSRS to call some T-SQL/proc on the server would be most appropriate. The problem I got now is when calling msdb.dbo.sp_start_job on remote SQL Server 2000 instances from local server using OPENQUERY or OPENROWSET I got Cannot following error.

process the object "exec msdb.dbo.sp_start_job @job_name = 'xxx' ". The OLE DB provider "SQLNCLI" for linked server "xxx" indicates that either the object has no columns or the current user does not have permissions on that object.

I guess this may because the sp_start_job doesn't return anything because I can use OPENQUERY/OPENROWSET to call other remote proc without problem. So any workaround?

update:

I have found it actually pretty simple in t-sql.

EXEC [linkedServerName].msdb.dbo.sp_start_job @job_name = 'test2'

So I don't need to use OPENROWSET/OPENQUERY atually since all the remote SQL Server 2000 instances are already added as remote servers.

I'd say the easiest possible way to do this is through PowerShell and SMO.  Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task.  But I'd take it a step further, such as error handling.  You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do.  Re-work it for your needs/environment.

As per RoKa's comment:

  Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want.  Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances.  I'm finding this information through this BOL reference.


Relational Database design best practice I am a student and I am trying to design a simple database, so if the ideas seem bad please let me know how to improve.

I have three tables in my mind, one for Professors and one for Students. Both of them have Contacts details. I need to know what is the best practice to achieve a relationship between the three.

I see two possibilities:
A) Having both Professors and Students tables use an auto-increment field and a supplementary default initialised field (let's say with 1 for Professors and 2 for Students). Then create a key in the third table (Contacts) as a combination of the unique ID and default initialized fields.

B) Having for tables instead of three, with two identical structure tables as ContactStudents and ContactProfessors, each serving for the appropriate table.

From my perspective I would choose the first one since if I would want to add new fields to the Contact table both would benefit from it.

Which one do you think is best and why? Is there another, better way, of achieving this?

EDIT:

After that variety of answers and at zagrimsan's suggestion I add the following requirements:
Student can be also a Professor.
A person (Student of Professor) can have multiple contacts.
A Contact can be reused for multiple Students and Professors (same kind) but not both Student and Professor at same time except the case when the same Student is also a Professor.

Problems involving classes and subclasses (or, if you prefer, types and subtypes) come up all the time in database design.  Your situation looks like a case in point.

In your case, here's what I would do, using a technique called Class Table Inheritance combined with a technique called Shared Primary key.

Create three tables:  Contacts, Professors, and Students.  Use the autonumber feature for the ID field of the Contacts table.  Have an ID field in the other two tables, but do not use the autonumber feature.  Instead, whenever you insert a new Contact, obtain the ID field just generated for the new contact, and provide that ID field as the value for ID in either Professors or Students.  The ID field is declared as the primary key in all three tables. You can even create a Contact that is both a student and a professor, if that makes sense in your situation.

You place other attributes in the appropriate table, according to the attribute, as you have suggested.

ID is guaranteed to be unique in all three tables.  If you want data about only students, join Contacts and Students matching on the ID field.  Likewise for data about professors.

Using shared primary key in this way enforces the 1:1 nature of the relationships between Students or Professors and Contacts.

Good luck!


Are there implicit indexes in InnoDB like MyISAM? If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:

(a)
(a, b)
(a, b, c, id)

Is this valid for InnoDB as well?

Or should I define the necessary ones explicitly?

This is not at all the case for InnoDB. Here is why:
For InnoDB table, A primary key is InnoDB is stored in gen_clust_index, the Clustered Index. Every secondary index has an internal key back to the gen_clust_index.
Given this background on PRIMARY KEY storage, now for differences:
MyISAM
Given Primary Key id and index a,b,c, the implicit keys are

(a)
(a, b)
(a, b, c)

This is always true of the index a,b,c regardless of the index being unique or non-unique. The column id is not known to the index. There is a rowid that links each .MYI entry back to a row in the .MYD, but but no direct knowledge of id. The id is quite visible in the primary key.
InnoDB
Given Primary Key id and index a,b,c, the implicit keys are

(a, id)
(a, b, id)
(a, b, c, id)

The reason id is known is due to the fact that every secondary index entry has an implicit key back to the gen_clust_index.
Please see my past posts on the gen_clust_index.
PROOF OF THIS...
Let's create two sample tables. One with MyISAM and the other InnoDB

Same Data
Same Columns
Same Indexes

Here is the code:
DROP DATABASE IF EXISTS pavel;
CREATE DATABASE pavel;
USE pavel
CREATE TABLE rolando_myisam
(
    id int not null auto_increment,
    a int not null,
    b int not null default 99,
    c int not null default 99,
    primary key (id),
    key abc_ndx (a,b,c)
) ENGINE=MyISAM;
CREATE TABLE rolando_innodb LIKE rolando_myisam;
ALTER TABLE rolando_innodb ENGINE=InnoDB;
INSERT INTO rolando_myisam (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
INSERT INTO rolando_innodb (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;

Here it is loaded:
mysql> DROP DATABASE IF EXISTS pavel;
Query OK, 2 rows affected (0.23 sec)

mysql> CREATE DATABASE pavel;
Query OK, 1 row affected (0.00 sec)

mysql> USE pavel
Database changed
mysql> CREATE TABLE rolando_myisam
    -> (
    ->     id int not null auto_increment,
    ->     a int not null,
    ->     b int not null default 99,
    ->     c int not null default 99,
    ->     primary key (id),
    ->     key abc_ndx (a,b,c)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE rolando_innodb LIKE rolando_myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE rolando_innodb ENGINE=InnoDB;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_myisam (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_innodb (a)
    -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
    -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
    -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
    -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
    -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

Here two queries to show a retrieval of the id columns:
SELECT id FROM rolando_myisam;
SELECT id FROM rolando_innodb;

Let's explain them:
mysql> EXPLAIN SELECT id FROM rolando_myisam;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_myisam | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Notice the Query optimizer chose the PRIMARY KEY for the MyISAM table, but chose the abc_ndx key for the InnoDB table. What does this show? The abc_ndx index for the MyISAM table cannot see the id column. Thus, id is not implicit for MyISAM.
Let's try a different type of query:
EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;

Look at the EXPLAIN plans:
mysql> EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | rolando_myisam | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

Again, abc_ndx cannot be used for getting id because it is not implicit for a MyISAM index. It is definitely implicit for InnoDB.
Let's try another different type of query:
EXPLAIN SELECT id FROM rolando_myisam where a = 12;
EXPLAIN SELECT id FROM rolando_innodb where a = 12;

Look at the EXPLAIN plans:
mysql> EXPLAIN SELECT id FROM rolando_myisam where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | rolando_myisam | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | NULL  |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>

Once Again,abc_ndx index cannot be used for getting id because it is not implicit for a MyISAM index. It is implicit for InnoDB. Note that the InnoDB lookup use the index only. This is why the Extra in the EXPLAIN plan had Using index for InnoDB, but had nothing for MyISAM. The MyISAM lookup had to reach into the .MYD to get id.


Allow access to specific user only I have a database with an owner for it. My problem is that, though I have assigned a password for the owner, any other database user can access this database locally, even without a password.

How can I configure the database so that only a specific user has access to it (only with right password)?

You will have to edit your pg_hba.conf file.  If you really want to reduce possible access to a single user (see the note below), you will need the following lines, and only these:

# TYPE  DATABASE  USER       ADDRESS   METHOD
host    all       dbowner    all       md5
local   all       dbowner              md5

This means that only dbowner will have access to any DB, logging in via the Unix socket or a TCP connection (from all addresses, including localhost).  You can replace 'all' with your DB name if you want to limit access to a single DB.

After having edited the file, you will have to reload the config - depending on your system, do a pg_ctl reload or kill -HUP <postmaster_pid>, or do this form inside the DB, by calling

SELECT pg_reload_conf();

Please note that in the future you might want to do certain things that need rights dbowner may not have (be it SUPERUSER, CREATEUSER or something else).  Depending on the policy you follow, you'll have to edit the file again to allow further access to a user (eg. postgres) that has the necessary privileges, or you have to assign these to dbowner before closing the door on other users.


sql server 2008 r2 DBCC checktable error This morning my database went into suspect mode.  I reverted back to last night's full backup to get it back online.  I went through the error log and found the last command that appeared to cause the suspect mode was on a MERGE on a particular table.  I ran DBCC CHECKTABLE on the table and got back the following

Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1253579504, index ID 7, partition ID 72057594230341632, alloc unit ID 72057594364887040 (type In-row data). Keys out of order on page (1:14254237), slots 184 and 185.
There are 366539002 rows in 3425599 pages for object "PJM_Prices".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'PJM_Prices' (object ID 1253579504).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (CaisoAnalyzer.dbo.PJM_Prices).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

in ERRORLOG

Internal database snapshot has split point LSN = 001935ad:00001bf6:0001 and first LSN = 001935ad:00001bf5:0001

Is there possibly a way to put just that table in single user mode or some other trick where I don't have to go into single user mode for the whole database?

One suggestion would be to identify the index that is causing the problem.

select o.name as tablename, i.name as indexname
  from sys.objects o
  join sys.indexes i
    on o.object_id = i.object_id
  where i.index_id = 7
    and o.object_id = 1253579504

and then drop it. If you believe you're going to need this index then I'd script out the DDL before dropping it, drop it, recreate it, and then rerun your DBCC. Since its index_id is 7 I'm going to assume that this table is potentially over-indexed so YMMV with actually recreating it.

If dropping the index doesn't work, then I would always recommend a last-known-good backup to which you can restore. Also, if this has been occurring for a while, the backup will also have the corrupted index since a full backup is a page-by-page copy of the database (+ some transaction log).


Very slow simple PostgreSQL query on RDS I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).

This is across a table of 4,152,928 rows..

select sum(some_field) c
from pages
where pages.some_id=123
and pages.first_action_at > '2014-01-01 00:00:00 +1000'

Total runtime: 45031 ms.
Locally, I have around 1.1million rows, and the same query takes about 450ms..

Here's the query plan, from explain:

Aggregate  (cost=475640.59..475640.60 rows=1 width=4)
   ->  Seq Scan on pages  (cost=0.00..475266.07 rows=149809 width=4)
         Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone)
                AND (some_id = 447))

Here's the response from explain analyze:

 Aggregate  (cost=475641.74..475641.76 rows=1 width=4) (actual time=42419.717..42419.718 rows=1 loops=1)
   ->  Seq Scan on pages  (cost=0.00..475267.22 rows=149810 width=4) (actual time=0.013..42265.908 rows=141559 loops=1)
    Filter: ((first_action_at > '2014-01-01 00:00:00'::timestamp without time zone) AND (some_id = 447))
    Rows Removed by Filter: 4011369

Total runtime: 42419.772 ms

For reference, 141559 rows are part of the sum().

The current indexes I have are:

:some_id
:some_id, :first_action_at

work_mem was previously set to 1 mb (RDS default). I've just changed this to 18 mb.

Edit: Seems to be resolved by upping work_mem as well as added the second index above, speed is now around 800 ms.

Matching index
After re-reading your question I realized you are not running Amazon Redshift, but Amazon RDS, which seems to be running unsullied Postgres, at least according to the documentation:

Amazon RDS supports DB instances running several versions of
PostgreSQL. Currently we support PostgreSQL versions 9.3.1, 9.3.2, and 9.3.3.

This would mean you have index-only scans at your disposal. If you meet some preconditions (basically if vacuum can keep up with write operations) and if some_field is not updated to often and reasonably small (which seems to be the case for a numeric column), the perfect index would include some_field in last position (like @zerkms first mentioned):
CREATE INDEX ON pages(some_id, first_action_at, some_field);

Note that some_id should come before first_action_at, because it is typically more efficient to have columns with equality checks first and ranges later. Details:
Multicolumn index and performance
If you don't see "index-only scan" in EXPLAIN ANALYZE, the last column is just ballast and better left away:
CREATE INDEX ON pages(some_id, first_action_at);

(Like you have now, according to your question update.)
Either way, another index on just (some_id) only offers very little over this multicolumn index:
Is a composite index also good for queries on the first field?
Server configuration
All the usual advice for slow queries and proper server configuration applies and a work_mem setting of 1 MB is much too low for a DB with millions of rows. But this particular setting should not be crucial for this particular query, since work_mem is (per documentation):

memory to be used by internal sort operations and hash tables.

Neither applies here.


Optimizing a simple query joining two big tables I don't understand why the following query is so slow (with Postgresql 9.6):

SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour
FROM traitements.sites_candidats AS s
JOIN traitements.zones_sites AS z
    ON z.pkid_site = s.pkid
WHERE s.statut = 'selected'
AND z.statut = 'selected';

The structure of the tables is the following:

CREATE TABLE traitements.sites_candidats (
    pkid serial PRIMARY KEY,
    statut varchar(255) NOT NULL,
    geom geometry(Point, 2154)
);
CREATE INDEX ON traitements.sites_candidats (statut);

CREATE TABLE traitements.zones_sites (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    geom geometry(MultiPolygon,2154),
    statut varchar(100)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut) ;
ALTER TABLE  traitements.zones_sites
    ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site) REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

(I deleted a couple of columns that are not involved in the query in order to improve readability).

The result of EXPLAIN ANALYZE:

Hash Join  (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1)
  Hash Cond: (z.pkid_site = s.pkid)
  ->  Seq Scan on zones_sites z  (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1)
        Filter: ((statut)::text = 'selected'::text)
        Rows Removed by Filter: 23
  ->  Hash  (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1)
        Buckets: 65536  Batches: 8  Memory Usage: 2191kB
        ->  Seq Scan on sites_candidats s  (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1)
              Filter: ((statut)::text = 'selected'::text)
              Rows Removed by Filter: 90265
Planning time: 0.386 ms
Execution time: 888.436 ms

It's obviously the JOIN .. ON that consumes time because it does a hash.

Table traitements.sites_candidats has about 300k rows (210k of them with statut='selected') and traitements.zones_sites about 210k rows (nearly 99% of have statut='selected'). The query takes more than 4 minutes.

Composite indices including statut might help.
Since only rows with statut = 'selected' are of interest, partial indexes might help some more:

-- CREATE INDEX ON traitements.zones_sites (pkid_site)
-- WHERE statut = 'selected';  -- not useful for you!

CREATE INDEX ON traitements.sites_candidats (pkid)
WHERE statut = 'selected';     -- probably also not useful, yet

Smaller indices, less write load and fragmentation.

However, since most rows in zones_sites have statut = 'selected', and since you fetch several columns from that table, and (almost) all rows, Postgres will stick to a sequential scan as fastest option and the only index that can help is the one on sites_candidats - where also ~ 70 % of all rows qualify. So a bitmap index scan using the above index would most likely still be more expensive than another sequential scan on zones_sites.

The remaining option to improve performance is an index-only scan. Consider appending the only other column of interest from sites_candidats to the index:

CREATE INDEX ON traitements.sites_candidats (pkid, geom)
WHERE statut = 'selected';

Only useful if preconditions for index-only scans are met, and
it becomes more attractive if the table is big and the appended column is small. Related:

Does PostgreSQL use an index-only scan in this JOIN?

More on index-only scans in my answer to your last question:

Optimizing query with type casting

Don't expect much, retrieving 210708 rows (according to your EXPLAIN output) will still take some time. If you don't actually need all of them, consider LIMIT (combined with ORDER BY) or additional predicates to fetch fewer rows and make it much faster.

Asides:

I would not use accented letters in identifiers (like: précision_contour). While that works, it introduces potential encoding problems.

Why different data types for statut? varchar(255), varchar(100). Seems like those should be the same. If only a handful of states are possible, make that an enum or a tiny FK column pointing to a statut table - to make everything smaller and faster, yet. And less error-prone.

You had Postgres 9.5 in your last question yesterday, but Postgres 9.6 today. Did you just upgrade? If so, did you run ANALYZE after the upgrade? Table statistics are not carried over. Related:

Optimizing slow performance of simple SELECT query


Blockchain (Bitcoin) as a database? I was reading this BBC News article and the following excerpt, caught my attention.  It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically included.
Is blockchain a potentially viable database solution for modern, high transaction volume applications?
It is pretty easy to see it's value for low volume transactions like personal medical records, but what about high volume databases?

What is blockchain?
Blockchains rely on cryptography to allow a set of computers to make changes to a global record without needing a central actor.
Removing the middleman cuts costs in almost every sector.
The blockchain is a ledger that records everything that happens to a collection of data known as a "block" in a chronological order or "chain".
As a currency this is an important feature because it allows users to be sure their digital money is one of a kind, the same way each note in your wallet is unique.
"Blockchain tech will be the way we create assets because it allows you to transfer digital information without copying," says Adam Ludwin, chief executive of Chain.com, which builds blockchain networks.
Blockchain can be used to track the history of all sorts of information and maintain its value, so, for example, doctors could use it to update medical records.
Since each change to a blockchain is made simultaneously across the whole network, no information is lost and because changes cannot be undone the system maintains its transparency. A special key is needed to make changes to each block, so individuals can keep their records safe by protecting that key.

I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all.

Using the blockchain as a live database:

Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes.  Basically a excel sheet without any computation capabilities that just gives you 'read/write' capabilities with lots of verification and validation.  A blockchain is a great way to validate your data is sanitized and correct before you put it in a database which let's you query it differently, index it, etc.

Benefits of the blockchain:

The blockchain in this case is purely a ledger and an API for PUT and GET requests.  That's about it.  The blockchain is interesting because you need a majority of nodes to pass the transaction as valid and there aren't any rollbacks, once it's committed it's committed.  Thus if someone tries to put in a fake transaction it will be caught unless the person doing it has a pool which has a strong majority share.  Then they can validate it in their pool before someone can reject it.  That is the strong point of the blockchain.  Verification that the data is accurate.  It is also typically pretty slow.  You're looking at about 10 minutes under normal load for it to get validated.  Under heavy load the time goes up quite a bit.

After you have validated that the transactions are valid and not fraudulent using the blockchain, you can then import that data into a database and work with it however you like.  I have some experience with this but note that every single transaction on the current bitcoin architecture will be recorded thus it has some interesting info to analyze.

Querying data out of the blockchain schema in a DBMS:

Here is the bitcoin diagram you can use to create the schema in PostgreSQL.  Using this you can then put it in a relational DBMS:
https://bitcointalk.org/index.php?topic=38246

This code repo is also helpful if you want to import the data into a real RDBMS:
https://github.com/bitcoin-abe/bitcoin-abe

As far as what DBMS you should put it in, that's up to your use case.  If you want to analyze the transactions/wallet IDs to see some patterns or do B.I. work I would recommend a relational DB.  If you want to setup a live ingest with multiple cryptocoins I would recommend something that doesn't need the transaction log so a MongoDB solution would be good.  I don't think you need to worry about Elastic Search unless you want to start doing live recording of all cryptocoins at the same time and will use it to do auto trading or something equally crazy. :)


innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition In one of my production environments, we have two instances running on a RedHat cluster, with  one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool occupied by instance1 & 12G occupied by instance2, which is not associated with the RedHat cluster. Both the data and transaction logs are located on the LVM disk partition with an ext3 file system.
For a performance boost and better I/O throughput I have decided to change innodb_flush_method to O_DIRECT.
With reference to the MySQL documentation:

Where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

Referring to High performance MySQL Ver 2 and 3, it stated that InnoDB developers found bugs with using innodb_flush_method=O_DSYNC. O_SYNC and O_DSYNC are similar to fsync() and fdatasync(): O_SYNC syncs both data and metadata, whereas O_DSYNC syncs data only.
If that all seemed like a lot of explanation with no advice, here’s the advice:

if you use a Unix-like operating system and your RAID controller has a battery-backed writecache, we recommend that you use O_DIRECT. If not, either the default or O_DIRECT will probably be the best choice, depending on your application.

By googling, I got this benchmark report: on O_DSYNC vs O_DIRECT

Bench Mark Report :
===================
1B Row Complex Transactional Test, 64 threads

 *   SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
 *   SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
 *   SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
 *   Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
 *   Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
 *   Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.

However, O_DIRECT disables the OS level cache, where double caching can be disabled which show some better I/O throughput.
Is it good to go with O_DIRECT rather than O_DSYNC? These two options are a bit confusing. Which option can show some better I/O throughput and enhancement in the performance without any impact on the data, reads/writes especially in production? Any better suggestions based on your personal experience?
I could see Rolando Update in the post:

Still there is slight confusion on both of these parameters. Where i could see most of the production config templates using O_DIRECT, i haven't seen any where recommending O_DSYNC.

System

MySQL 5.1.51-enterprise-gpl-pro-log
Red Hat Enterprise Linux Server release 5.5
DELL DRAC with Raid Controller having a battery write back cache 512MB
Dell PERC controllers H700 with a battery back-up unit (BBU).

Additional information

mysql> show variables like 'innodb_thread_concurrency';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 96    |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_read_io_threads';
Empty set (0.00 sec)

mysql> show variables like 'innodb_write_io_threads';
Empty set (0.00 sec)

We are using the default plugin, so I have posted the info from InnoDB status:

mysql> SELECT * FROM Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE'\G
*************************** 1. row ***************************
           PLUGIN_NAME: InnoDB
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 50151.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Innobase OY
    PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys
        PLUGIN_LICENSE: GPL
1 row in set (0.00 sec)

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

  As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two ways:

Upgrade to MySQL 5.5 (All InnoDB Plugin enhancements are part of MySQL 5.5)
Upgrade the InnoDB Plugin

Once you have done so, you can enhance InnoDB to

access more CPUs and Cores
increase read and write I/O threads
scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian


Changing Column Data Type with FKs I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because this table has millions of rows. Is this possible while not losing the FKs dependencies and any data?

It is a Test environment. The reason I want to do this is because I think I'm getting locks because of datetime precision (milliseconds). I have many rows with the same datetime and not ordered.

It's possible to change the data type without data loss or breaking FK dependencies.  It depends on whether you have some downtime where you can execute the required change though.

Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk.  This can take a little time, depending on how many rows your table has (you said "millions").

The change would look like this in T-SQL (you should definitely do things like this in T-SQL rather than the GUI, by the way):

ALTER TABLE dbo.YourTable ALTER COLUMN CreationMoment datetime2(7);

The ALTER statement here requires SCH-M (schema modification) locks: on the table being changed, and on the tables that have foreign key relationships to this one.  That means that this statement will wait until there are no concurrent transactions before it's able to run.

Once it starts running, it will also block any concurrent activity on the tables until it completes.

Here's a toy example that creates a table with 10,000,000 rows with dates, and another table with 1,000 rows referencing the first table:

USE [master];
GO
IF EXISTS (SELECT null FROM sys.databases d WHERE d.[name] = '248612')
    DROP DATABASE [248612];
GO

CREATE DATABASE [248612];
GO
USE [248612];

CREATE TABLE dbo.DateTest
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    SomeDate datetime NOT NULL
);

CREATE TABLE dbo.OtherTable
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    DateTestId int NOT NULL,

    CONSTRAINT FK_OtherTable_DateTestId
        FOREIGN KEY (DateTestId)
        REFERENCES dbo.DateTest (Id)
);

INSERT INTO dbo.DateTest
SELECT TOP (10000000)
    DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2019-01-01')
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;

INSERT INTO dbo.OtherTable
SELECT TOP (1000)
    Id
FROM dbo.DateTest;

That code takes about 30 seconds to run on my machine.  With no other concurrent activity, the ALTER statement takes about 10-15 seconds:

ALTER TABLE dbo.DateTest ALTER COLUMN SomeDate datetime2(7);

Ideally, you could do this kind of thing on a test system to assess about how long the blocking will be in production once the ALTER begins.

Regarding my "concurrent activity" statements, if I open a transaction and update a row in dbo.DateTest, the ALTER won't even start.  It sits and waits on the SCH-M lock that it needs, which you can see by running sp_WhoIsActive @get_locks = 1:

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

If I commit or rollback that update transaction, the ALTER starts, and I can see that it takes out locks on the DateTest and OtherTable tables (abbreviated XML here):

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="FK_OtherTable_DateTestId" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="OtherTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

This will block writes on the tables involved until the ALTER completes.


Partition Switch - What is the benefit if staging has to be same as destination table I am exploring to load large datasets into destination tables using SSIS.
I came across "Partition Switching" as one of methods to load data faster into destination tables.

But since the staging table has to be of the almost same configuration as destination table partition in order for partition switching to work, what is the real benefit here?

My concern is loading of the data after the cleanup/transformations. If staging table is exactly same as destination partition, loading time should be same for both. Or is it that loading data into a huge partitioned clustered indexed table will take more time versus loading into exactly same empty un-partitioned clustered indexed table?

We still have to fill up the staging table (which is almost same as destination), it's going to take the same time as to load data directly in destination partition, right? Why not load directly into destination then?

What is real benefit in terms of time to load the data here?

Speed is the name of the game here. You can load large amounts of data into a staging table then switch it in, this reduces the amount of contention for locks/resource on the primary table (though note when you do switch back in, though very fast it does require a lock on the main table). It allows you to manipulate and sanitize your data whilst in staging so you're not hitting the main table unnecessarily post import. The initial load from source can be quicker too, so if that is a bottle neck for you, it could be a benefit.

Just as a rough example of time I used to work with a large database where flat files were loaded into a DB daily, about a million + rows in a load. Inserting directly into the table in batches took around 4 hours. With a table partitioning strategy loading to stating and switching in took 1 hr 30 mins. And only < 1 min of that was the switch that hit the primary table.

Every situation is different, experiment and play around, see if its worth it, even if its not its a good bit of experience.

  Or is it that loading data into a huge partitioned clustered indexed table will take more time versus loading into exactly same empty un-partitioned clustered indexed table?

Yes and no, its to do with the indexes and the amount of data already in the table if your indexes are not partition aligned (partitioned indexes) they should be to get full benefit of partitions anyway, without them inserts would take a massive hit, also data being accessed from the primary table, if the queries do not eliminate partitions that's going to cause issue while inserting too. another benefit of using staging is if the load fails you just have to drop the staging table, not clean up the primary.


How to create custom error messages in APEX? I would like to replace standard ORA-xxxxx messages with custom ones in Oracle Application Express. For example instead of this:

to have something like "You have left some fields blank, try again".
What is a best way to do this?

In Apex specifically, use validations. You can specify them per field or use a custom one that checks all the fields at once.

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldr_validate.htm#BABCDBDE


Count consecutive rows in mysql I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current location. I want to know how long time a vehicle halts in certain location.

Will explain by below table, it is the latest data

id gadget_id  location  submitted_date
   ---------  --------  ----------
1  1          calicut   2012-07-15
2  1          calicut   2012-07-14
3  1          calicut   2012-07-13
4  2          thrissur  2012-07-12
5  1          calicut   2012-07-11
6  1          kannur    2012-07-10
7  2          thrissur  2012-07-09
8  1          calicut   2012-07-08
…          …         …

*I want to know how long time gadget_id 1 halts in position calicut.*

Here we can show that the gadget_id = 1 and position=calicut last 4 data's comes from same position calicut. The next data of the gadget_id=1 is from kannur, so we avoid the data from this id. How to get the count 4 when we give the input gadget_id = 1 and position=calicut

Anybody give the suitable query, expect a single query without joins.

Since gadget_id is a vehicle, you need to monitor two things as you look at each row

when a gadget_id switches location
when a gadget_id switches to another gadget_id

The solution lies in organizing a set of user variables to monitor that change. Please forgive you are about to see:
First, let's load your data in the test database in a table called gadget_location:
mysql> use test
Database changed
mysql> drop table gadget_location;
Query OK, 0 rows affected (0.07 sec)

mysql> create table gadget_location
    -> (
    ->     id int not null auto_increment,
    ->     gadget_id int,
    ->     location  varchar(30),
    ->     submitted_date date,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into gadget_location (gadget_id,location,submitted_date) values
    -> (1,'calicut' , '2012-07-15'), (1,'calicut' , '2012-07-14'),
    -> (1,'calicut' , '2012-07-13'), (2,'thrissur', '2012-07-12'),
    -> (1,'calicut' , '2012-07-11'), (1,'kannur'  , '2012-07-10'),
    -> (2,'thrissur', '2012-07-09'), (1,'calicut' , '2012-07-08');
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from gadget_location;
+----+-----------+----------+----------------+
| id | gadget_id | location | submitted_date |
+----+-----------+----------+----------------+
|  1 |         1 | calicut  | 2012-07-15     |
|  2 |         1 | calicut  | 2012-07-14     |
|  3 |         1 | calicut  | 2012-07-13     |
|  4 |         2 | thrissur | 2012-07-12     |
|  5 |         1 | calicut  | 2012-07-11     |
|  6 |         1 | kannur   | 2012-07-10     |
|  7 |         2 | thrissur | 2012-07-09     |
|  8 |         1 | calicut  | 2012-07-08     |
+----+-----------+----------+----------------+
8 rows in set (0.00 sec)

mysql>

OK, here comes the mess:
SET @dupcount = 0;
SET @group_number = 0;
SET @cur_gadget_id = 0;
SET @cur_location = MD5("1");
SET @cur_gadget_location = MD5("1");
SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
(
SELECT
    *,
    @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    @cur_gadget_location := gadget_location
FROM
(
    SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    (
        SELECT *,
        @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
        @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
        FROM gadget_location
    ) AAA
) AA ) A GROUP BY gadget_id,location,GroupNumber;

Want to see it work ??? Here it goes:
mysql> SET @dupcount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group_number = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_gadget_id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cur_gadget_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
    -> (
    -> SELECT
    ->     *,
    ->     @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    ->     @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    ->     @cur_gadget_location := gadget_location
    -> FROM
    -> (
    ->     SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    ->     (
    ->         SELECT *,
    ->         @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
    ->         @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
    ->         FROM gadget_location
    ->     ) AAA
    -> ) AA ) A GROUP BY gadget_id,location,GroupNumber;
+-----------+----------+-------------+-------------+
| gadget_id | location | GroupNumber | DaysStopped |
+-----------+----------+-------------+-------------+
|         1 | calicut  |           1 |           3 |
|         1 | calicut  |           3 |           1 |
|         1 | calicut  |           6 |           1 |
|         1 | kannur   |           4 |           1 |
|         2 | thrissur |           2 |           1 |
|         2 | thrissur |           5 |           1 |
+-----------+----------+-------------+-------------+
6 rows in set (0.02 sec)

mysql>

According to this output, here is what you have:
Gadget 1

Group 1 : stopped at calicut for 3 days
Group 3 : left and came back for 1 day
Group 4 : left calicut and went to kannur for 1 day
Group 6 : left kannur and went to calicut for 1 day

Gadget 2

Group 2 : stopped for thrissur for 1 day
Group 5 : left and came back for 1 day

For some reason, the GroupNumbers came out different in SQLFiddle`. Notwithstanding, the rest of the output is the same.
I hope this is right ...


Strange behavior of query with pg_sleep I have plpgsql anonymous block. PostgreSQL 10. I store current time to variable, raise notice with value of the variable, sleep for 20 seconds, store current time to another variable, store both variables into table.
What I expect to see: difference in time between 1st and 2nd variables would be 20 seconds. Instead, I see what query execute for 20 seconds as expected, but values of variables are same. How it can be?

Code to reproduce:

CREATE schema if not exists tmd;

CREATE table if not exists tmd.gt_recommended_log (
    id bigserial NOT NULL,
    start_ts timestamp NOT NULL,
    end_ts timestamp null,
    CONSTRAINT gt_recommended_log_pkey PRIMARY KEY (id)
);

delete from tmd.gt_recommended_log;

do $$
declare _a timestamp;
declare _b timestamp;
begin
   _a := now();
  raise notice '%s', _a::varchar(30);

  perform pg_sleep(20);

  _b := now();

  insert into tmd.gt_recommended_log (start_ts, end_ts)
      values(_a, _b);

  raise notice '%s', _b::varchar(30);
  raise notice '%s', now()::varchar(30);

end ; $$ language plpgsql;

select * from tmd.gt_recommended_log;

Found explanation.

Now() contains timestamp of transaction start.

It is necessary to use TIMEOFDAY().


It appears that MariaDB (and MySQL for that matter) attempts to implicitly cast operands of bitwise operators to integers, and it is known MySQL behaviour when converting strings to integers to ignore the rest of the string if it encounters non-digits, e.g. cast('123abc' as integer) will yield integer 123, and cast('ff' as integer) will yield 0.
This is a feature of the bitwise operators and bit functions, not the BIT type itself, as stated in the MySQL manual (I assume MariaDB inherits this behaviour):

Currently, bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types are converted to BIGINT and truncation might occur.

It looks like this behaviour is going to change in MySQL version 8:

An extension for MySQL 8.0 changes this cast-to-BIGINT behavior: Bit functions and operators permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits.

which will bring it in line with your expectations.


SERIALIZABLE transaction information leak via serial I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates:

CREATE TABLE foo (id serial, n integer);

-- connection 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

-- connection 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
insert into foo (n) values (1),(2),(3);

-- connection 1
select nextval('fun_id_seq') - (select count(*) from fun) as leak;

leak
----------
       4
(1 row)

This looks like a dirty read. If I was to run the transactions serially I would not get this result. Why is this then considered SERIALIZABLE?

Thanks

For various performance and concurrency reasons, sequences are non-transactional, but atomic. In other words, they always give a distinct value to each session, but they don't reuse values, nor are they "gapless" where they return a continuous sequence of numbers.

The second transaction in your example was handed distinct sequence values for each of the rows inserted, and since that is atomic, but non-transactional, the first transaction sees the sequence's nextval increased even though the transaction isolation level is SERIALIZEABLE.

The behavior is noted at the bottom in the Sequence Manipulation Functions documentation.

Neil Conway also explains this quite well in his post FAQ: Using Sequences in PostgreSQL, including a link to a method of creating a "gapless" sequence that should behave how you had initially expected, but at the cost of some concurrency and performance.


Why if I add one more condition on WHERE clause, then using an index fails? Here is my query:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = 29
AND question.amount IS NULL
AND answer.id not in (
  select post_id
  from votes
  where table_code = 15
  group by post_id
  having sum(value) < 0)

And this is the EXPLAIN result of it:

As you see, all tables are using an index. Ok, now I need to add one more condition on the WHERE clause of outer query. This is that condition:

... AND from_unixtime(answer.date_time) BETWEEN (now() - INTERVAL 1 year) AND (now() - INTERVAL 1 hour)

Well, after adding that new condition, this is the EXPLAIN result of it:

See? answer table doesn't use any index anymore. Why? And what index do I need to make query above faster and more efficient?

Perhaps you already know, but Using index confuses a lot of people -- they assume it means the query is using an index, and that's not what it means.

That means the query is using the index as a covering index -- reading row data from the index, ignoring the main table because the index contains all the necessary data, which is generally assumed to be faster since the amount of data to be read should be smaller.

Adding the extra condition -- which is not sargable, but that's a second problem -- defeats the covering index, which is expected, but it also triggers a full table scan (type is ALL and key is NULL), which seems counter-intuitive since this can still be resolved with an equality ref against the index and evaluating the condition Using where... and this is probably because you're testing against a small data set.  With more rows, the optimizer might choose a different path.

An index on (author_id, date_time) would be useful -- and might even switch the query back to Using index, though it wouldn't be optimal, because -- as I mentioned -- it's not sargable.

Except in very limited circumstances, you don't want to use a column as the argument to a function in the WHERE clause, because this must be evaluated by scanning or at least filtering.

Assuming answer.date_time is an INT UNSIGNED column containing the unix epoch time of the event (otherwise using FROM_UNIXTIME() here is incorrect, anyway) the more desirable expression is this:

AND answer.date_time
    BETWEEN UNIX_TIMESTAMP(now() - INTERVAL 1 year)
        AND UNIX_TIMESTAMP(now() - INTERVAL 1 hour);

The value of NOW() is frozen when a query begins executing, no matter how long the query takes to run.  Because of this fact, UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR) is resolvable to a constant, as is UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR).  The optimizer will resolve both of these to integer constants, and then can use an index including the date_time column to identify the specific rows using a range scan.


I Want To insert data in transaction table and at the same time update customer table in codeigniter (codeigniter) I Want To insert data in transaction table and at the same time update customer table .
I want to Add value of ChangeDue in transation table To value now_debt in Customer Table

Function in transaction Model

public function add($_iN, $_iC, $desc, $q, $qs, $_up, $_tp, $change_due,
$cn) {
$data = ['itemName' => $_iN, 'itemCode' => $_iC, 'description' => $desc,
'quantity' => $q, 'quantitys' => $qs, 'unitPrice' => $_up,
'totalPrice' => $_tp,'changeDue' => $change_due,'cust_id'=>$cn];
     $this->db->insert('transactions', $data);
     $this->db->set('customer.now_debt', 'customer.now_debt' + $change_due);
     $this->db->where('customer_id', $cn);
     $this->db->join('transactions', 'transactions.cust_id =
     customer.customer_id');
    $this->db->update('customer');
    if ($this->db->affected_rows()) {
        return $this->db->insert_id();
    }
    else {
        return FALSE;
    }
}

Codeigniter does not support join for update in active records. You need to update your code as follows:

public function add($_iN, $_iC, $desc, $q, $qs, $_up, $_tp, $change_due,
$cn) {
$data = ['itemName' => $_iN, 'itemCode' => $_iC, 'description' => $desc,
'quantity' => $q, 'quantitys' => $qs, 'unitPrice' => $_up,
'totalPrice' => $_tp,'changeDue' => $change_due,'cust_id'=>$cn];
     $this->db->insert('transactions', $data);
     $updateSuccess=$this->db->query('customer JOIN transactions ON transactions.cust_id = customer.customer_id SET customer.now_debt = customer.now_debt + ? where customer_id = ? ' , array($change_due,$cn));
    if ($updateSuccess) {
        return $this->db->insert_id();
    }
    else {
        log_message('error',$this->db->error());
        return FALSE;
    }
}