Wrong query results on column ORA_ROWSCN with bind variables and NVL (Oracle 12c) In the process of migrating our DB from Oracle 11 to Oracle 12c (12.2.0.1.0), we discovered that the same query gives different results on the two instances.

The query is:

VARIABLE myId NUMBER;
BEGIN :myId := 1325; END;
/

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

On Oracle 11, we have:

      RID ORA_ROWSCN
--------- ----------
     1325 1.3439E+13
     1325 1.3439E+13

while on Oracle 12c we have:

      RID ORA_ROWSCN
--------- ----------
     1325
     1325

and the following query

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(1325, rid);

gives:

      RID ORA_ROWSCN
--------- ----------
     1325    2549788
     1325    2549788

(Clearly the different values of the ORA_ROWSCN column are expected.)

MY_TABLE.RID is NUMBER(38) NOT NULL and there is a non-unique index on it.

I strongly suspect that the wrong results are the unintended consequences of the optimizer, but, as I have a very limited experience with DBs, I have no clues on how to fix this strange behavior.

The execution plans of the two queries are:

Plan hash value: 709374914

---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    52 |   676 |     2   (0)| 00:00:01 |
|   1 |  VIEW               | VW_ORE_55EECC8D |    52 |   676 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                 |       |       |            |          |
|*  3 |    FILTER           |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| MY_TABLE_INDEX1 |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    FILTER           |                 |       |       |            |          |
|   6 |     INDEX FULL SCAN | MY_TABLE_INDEX1 |    51 |   663 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:MYID IS NOT NULL)
   4 - access("RID"=:MYID)
   5 - filter(:MYID IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

and

Plan hash value: 460232730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE        |     2 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | MY_TABLE_INDEX1 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RID"=1325)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

My question is: how can I obtain on Oracle 12c the same results that I obtained so far on Oracle 11?

The plan clearly shows the OR-expansion query transformation. It is not the first time it produced wrong results due to various bugs.

Try running your query as:

SELECT /*+ NO_EXPAND */
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

Or set the below parameters before executing it:

alter session set "_no_or_expansion"=true;
alter session set "_or_expand_nvl_predicate"=false;

Then run your query.


Removing values from array in postgreSQL Lets say I have a table called users, who looks something like the following:

users table:

|user_id | name |
   1        Bob
   2        Alice
   3        Joe
   4        Tom

And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY[]::integer[])

books:

name     num_pages     users
mybook1  130           {4,2,1,44}
mybook2  140           {1,2}
mybook3  145           {5,7,8,10}

unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.

My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table? In this case I would like to remove any user id who is not 1/2/3/4 from the users column

for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table

(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)

note: I have no foreign keys or something like that in the books table

Thanks a lot

This should perform nicely:

UPDATE books AS b
SET    users = bu.users
FROM  (
   SELECT b1.name, u.users
   FROM   books b1
   CROSS  JOIN LATERAL (
      SELECT ARRAY(
         SELECT user_id
         FROM   unnest(b1.users) WITH ORDINALITY u(user_id, ord)
         JOIN   users u USING (user_id)
         ORDER  BY u.ord
         )
      ) u(users)
   ) bu
WHERE   b.name = bu.name
AND     b.users <> bu.users;

Preserves original order of array elements. If you don't need to, drop ORDINALITY & friends from the query.

Can result in empty arrays.

Assuming ...

books.name is the PK. Else use some other UNIQUE column from books instead.
users.user_id is at least UNIQUE.
books.users does not contain NULL values. Else those are eliminated as well.


Postgres 9.1.6 Error index contains unexpected zero page at block 0 I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine.

When I try to run a query on the replica DB I get the error below:

ERROR:  index "tbl_cust_id_idx" contains unexpected zero page at block 0
HINT:  Please REINDEX it.

What might be causing this error?

The question is also posted in https://stackoverflow.com/questions/17865135/postgres-9-1-6-error-index-contains-unexpected-zero-page-at-block-0

I just ran into the same issue on Postgres 8.3.11. Although I could not identify the root cause, the fix was simple enough:

REINDEX INDEX tbl_cust_id_idx;

This page contains hints as to what may have caused the error, although they are vague:

  An indicated table index was corrupted (may be a result of recent postgres or system failure, there was a lack of free space on the device, etc).

Ideally it would be helpful if there was something that could be put in place to proactively prevent or correct these errors. On the bright side, this is the first time we have ever seen this problem in several years and across hundreds of machines.


How can I trace the cause of a memory spike in Postgres? The title tells the story, but how can I trace the cause of a memory spike in Postgres 11.10? As you can see from the screen shot below there was a memory spike all the way up to 32 GB which caused the OOM to start and kill a bunch of connections. According to /var/log/postgresql/postgresql-11-main.log the database system went into recovery mode at the same time and we lost quite a bit of data as well.
I suspect that the number of connections coupled with very large insert statements might have something to do with it, but need a way of proving (or disproving) the hypothesis.

First, you should configure Linux so that it does not overcommit memory, because there's nothing you can get from a backend that is killed by the OOM killer.
Set vm.overcommit_memory = 2 and set vm.overcommit_ratio to the value calculated as
100 * (RAM - swap) / RAM

Then you should get an OOM error from PostgreSQL rather than PostgreSQL going into crash recovery.
Such an OOM error will cause a memory context dump in the PostgreSQL log file, so you can see which memory context uses all that memory. Also, you get the query that caused the error, so you can experiment with that.


Cannot reset MySQL default root password after initial installation I have installed mysql 5.7 on a Red Hat Enterprise Linux 5.5 server and when I tried to run following command after installation it is asking for a password for root@localhost.

/usr/bin/mysql_secure_installation

I have never set a root password and I'm logged in to Linux as root user.

Then I have found following documentation for resetting root password and followed that one too.

https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

But even after starting the server with following command it is asking for a password

/etc/init.d/mysqld start --skip-grant-tables &

I have tried starting the server with init-file option, but no success.

Does anyone know how to reset the password correctly or default MySQL password?

Restart the service without authentication

Ubuntu/Debian

service mysql stop
service mysql start --skip-grant-tables --skip-networking

Other sysv

If you're on a sysv Linux use this

/etc/init.d/mysqld stop
/etc/init.d/mysqld start --skip-grant-tables --skip-networking &

Resetting

Then you need to login as root

mysql -u root -p

Then when logged in run

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Difference between other guides

There are a lot of "guides" on this, for clarity,

Some guides use the older SET PASSWORD syntax, that's pre 5-7.6, don't use that anymore.
Some guides just don't use --skip-networking, that's unsafe.
Some guides don't connect with the -u root that's more error-prone.


Check and count if a column value is used in another column of same table I have a table like this

and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0, also the 11/4/19 entry of 1 is ignored as the check is 0, so 9/4/19 entry is shown in result.

Next is the children count. it counts the number of times,the parent id is repeated. I really don't want the number of  times,the parent id is repeated,but just want to know if its has child or not.

I am confused how to get the children count. Remaining i did like this

SELECT id, modified,checks
FROM ( select *
       from test
       order by modified desc) as t1
where checks!=0  group by (id)

and i got this result

How to get children count too or just if the id got a child?
How to get a result like this

SELECT t1.id,
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;

fidddle


MySQL Continue Handler Problems inside a Function I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I still end up with a warning "No data - zero rows fetched, selected, or processed". My code is below, what am I doing wrong? I really want to get rid of this warning :)

DROP FUNCTION IF EXISTS `getCompanySetting`;
DELIMITER |

CREATE FUNCTION `getCompanySetting`(setting_company_id INT, setting_name VARCHAR(255))
    RETURNS TEXT
BEGIN
    DECLARE setting_value TEXT DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET setting_value = NULL;

    SELECT
        value
    FROM company_settings
    WHERE
        company_id = `setting_company_id`
        AND
        name       = `setting_name`
    INTO setting_value;

    RETURN setting_value;
END|

DELIMITER ;

mysql> SELECT getCompanySetting(24, 'observers_active');
+-------------------------------------------+
| getCompanySetting(24, 'observers_active') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Update: MySQL Version 5.5.25

Before  MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked.

I have explained this kind of behavior and the solution in this post:

Event Scheduler: No data - zero rows fetched, selected, or processed


How to optimize a query that includes counts of rows in multiple tables? My query looks like this:

EXPLAIN SELECT ...

FROM USER u
LEFT OUTER JOIN
 (SELECT COUNT(*) notified_count, user_id
  FROM user_email_tracking ON u.id = email_tracking
  WHERE email_type_id = 4
  GROUP BY user_id) email_tracking ON u.id = email_tracking.user_id

JOIN ( SELECT user_id, count(*) as subscriptionCount
       FROM user_filter
       GROUP BY user_id) uf on u.id = uf.user_id

WHERE u.stage = 10

My intuition is this is pretty doable.  That u.stage = 10 narrows it down a lot. There's only a few hundred users who it applies to.  If I could narrow down by that first then perform all other work, seems like this query should take a few seconds.

But - user_filter is huge, and user_email_tracking is pretty big. (Both are indexed on their user_id columns). If those have to get performed on everything, it's much slower.  And I also must avoid joining user_filter against user_email_tracking on user_id, since that would be a huge times a huge table.

If I only had one count to do, I could move the group by all the way outside and just have a row for each row of user_filter (or user_email_tracking).

How to get SQL to do these things in the right order?

Engine is InnoDB.

My first instinct is use a big old join, count distinct on the ids of the secondary tables, and let the optimizer do it's thing.  Note, the tables you want to count should have a unique primary key.  I've assumed they are a single column with the name "id" for both tables in the query below.

SELECT ...
FROM USER u, COUNT(distinct et.id) as et_count, COUNT(distinct uf.id) as uf_count
LEFT OUTER JOIN user_email_tracking et
    ON u.id = et.user_id
LEFT OUTER JOIN user_filter uf
    ON u.id = uf.user_id
WHERE u.stage = 10
    AND et.email_type_id = 4
GROUP BY u.id

That said, my other instinct is to put your subqueries into temporary tables with indexes. like...

CREATE TEMPORARY TABLE users (id INT PRIMARY KEY)

INSERT INTO users_temp (id)
SELECT id
FROM USER u
WHERE u.stage = 10

CREATE TEMPORARY TABLE email_tracking_count (user_id INT PRIMARY KEY, notified_count)

INSERT INTO email_tracking_count (user_id, notified_count)
SELECT u.id, COUNT(et.id)
FROM users_temp u
LEFT OUTER JOIN user_email_tracking et
        ON u.id = et.user_id
WHERE et.email_type_id = 4
GROUP BY u.id

CREATE TEMPORARY TABLE user_filter_count (user_id INT PRIMARY KEY, subscription_count)

INSERT INTO user_filter_count (user_id, subscription_count)
SELECT u.id, COUNT(uf.id)
FROM users_temp u
LEFT OUTER JOIN user_filter uf
        ON u.id = uf.user_id
GROUP BY u.id

SELECT ...
FROM users_temp ut
LEFT OUTER JOIN email_tracking_count etc
        ON ut.id = etc.user_id
LEFT OUTER JOIN user_filter_count ufc
        ON ut.id = ufc.user_id

That's a good bit longer, but I have a feeling it may perform better.  Try both out and let me know which works better.  I'll edit my answer accordingly.

disclaimer: I didn't run these queries, so theres probably a syntax error or typo somewher.


Error: File '' couldn't be removed (error 2: No such file or directory) I get the following pgAdmin III error every time I restore a database, using  pgAdmin III:

Error: File '' couldn't be removed (error 2: No such file or directory)

See the pgadmin log.

My configuration:

  PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
  4.8.2-19ubuntu1) 4.8.2, 64-bit

  Linux 3.19.0-26-generic #28~14.04.1-Ubuntu SMP Wed Aug 12 14:09:17 UTC
  2015 x86_64 x86_64 x86_64 GNU/Linux

This bug was reported by Byaruhanga Franklin to the mailing lists,

  I also tried with pgsql and pg_restore didn't get any error. i then changed to a lower version of PGADMIN3 1.18 still didn't get any error. i then built the new version 1.22.0 Alpha 2 from the git repository. this also has no error. i guess the problem is limited to only pgAdmin III Version 1.20.0.

Upgrade to PgAdmin3 1.22, or to PgAdmin4.


Best way to model a single user with multiple usernames I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames.

The context is a social gaming website. This may result in a user wishing to have more than one username. For example, one username for playing within a team (prefixed or similar) and one for playing alone.

I've been trying to rack my brains over the best way to represent this in my database, so far I've come up with:

In this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

I guess with this one, we're more or less guaranteed to have a username for each user, and the owned usernames table provides a list of usernames owned by the user. It feels better, but then there's duplicate information (the username is specified in two tables when active).

Anyone have better ideas of how I can structure this? Is there a common pattern for this?

Essentially, what I am envisaging is some way of providing functionality to my website of having one user per person, logging in with an email and password, and linking one or more usernames to that user.

I guess a 1:N relationship between user and username where N > 0. I'm just not sure about how to model this effectively. Or indeed, if this is a sign that my desired functionality needs re-thinking.

There will be no formal notion of a team, this was just an example where a person would maybe want more than one username.

Of course, we could simplify this and force people who want separate usernames to sign up multiple times. This would simplify the database a huge amount, but it'd be more convenient to the user to allow one login for multiple usernames.

I'm thinking of limiting this to start with to a maximum of 2 or 3, but I guess this doesn't change the design, just a front end limitation.

You have clarified that you will not implement the team aspect in your system, but I consider that keeping the suggested approach for such scenario in this answer is still pertinent since it can be a useful example for other seekers.
Along with that, I have included some other contexts in which you could offer the option of having multiple Usernames (or Nicknames, or Handles), and a possibility that takes into account the common ASP.NET arrangement for dealing with Users and Roles data. These elements may serve to establish some analogies with the informational requirements of your own business environment.
1. User Profiles and Team Player Nicknames
1.1 Business rules
According to my understanding of your description about the social gaming website, I consider that the following conceptual-level formulations are specially relevant:

A User may be a Player of zero-one-or-many Teams
A Team is made up of one-to-many Players, each of which must be a User

1.2 IDEF1X diagrams
Then, I derived from those business rules the two IDEF1X1 diagrams that are presented in Figure 1.

1.2.1 Option A
As you can see in such a diagram, there is a many-to-many (M:N) association or relationship between the entity types Users and Teams, which takes effect in the associative entity type called TeamPlayer.
The UserProfile.UserId PRIMARY KEY (PK for brevity) property migrates2 to TeamPlayer as PlayerId, a role name that I have assigned to UserProfile.UserId in order to make it more meaningful in the context of its corresponding entity type, and these properties must be connected via a FOREIGN KEY (FK) defined in TeamPlayer.
On the other hand, TeamPlayer.TeamNumber is associated to Team.TeamNumber by virtue of another FK reference.
With this arrangement, one given UserProfile can hold

one Username when acting as an individual, and
one Nickname when he or she is carrying out the role of a Player for each of the Teams in which the UserProfile in question has joined.

1.2.2 Option B
This diagram is quite similar to the one referred in Option A, but it has an important aspect that is different, since I have moved the TeamPlayer.Nickname property to a separate entity type, which is called Nickname. This configuration is useful in case the Nickname value of a Team Player is optional, i.e., TeamPlayers may choose to provide or not provide a Nickname.
1.3 Team Player Nickname uniqueness
The Option A daigram has an ALTERNATE KEY3 (AK) defined for TeamPlayer, which is composed of two properties: TeamNumber and Nickname. In turn, the Option B diagram exhibits an AK in the Nickname entity type that is made up of TeamNumber and Nickname, as well. In this manner, both methods would provide UNIQUEness for a Nickname value within the context of a determined Team instance.
If you want to prevent the same Nickname value from being repeated in several Team occurrences, then you should set up an AK comprised of only one single property, that is, Nickname. This approach is valid for option A and Option B.
Alternatively, if you want to have the Nickname property set as NULLable (i.e., optional), you could define a “filtered” (or “conditional”, or “partial”, depending on the platform of use) UNIQUE constraint in Option A for supplying Nickname uniqueness in relation to a Team but, as I see it, proceeding this way things would get less elegant, so to speak.
2. People, User Profiles and Usernames
I created some more IDEF1X diagrams (presented in Figure 2) depicting different scenarios in which a given Person can have multiple Usernames, aiming to broaden the scope of this post. They are shown in the following section.
2.1 IDEF1X diagrams

2.1.1 Option A
This option is representing the following conceptual business rules:

A Person may log in via zero-or-one UserProfile
A UserProfile has one-to-many Usernames

Thus, it is comparable to your first diagram, but I have added a Person entity type that is optionally connected to UserProfile via the UserId FK definition.
Another important addition is the Username PK, which is comprised of

a FK reference to UserProfile.UserId and
the CreatedDateTime property that represents the instant in which a given Username was inserted.

This allows you to get rid of the Username.UsernameId (or username.id, if you prefer) property.
In this regard, this excerpt from your question is very important:

So, in this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

Yes, you should establish a policy that states how many Username instances can have the IsActive property set to 'TRUE' with respect to a precise UserProfile, whether exclusively one Username that is “active” at a given point in time, perhaps two, three, etc., or none at all. As you know, each possibility demands distinct validation methods.
But, if a certain User can have multiple Usernames, I do not think that there can be a problem if such a User does not have any “active” Usernames, since the log in credentials are the Email and Password. In this case, when no Username is “active”, you could display some sort of a “default message” indicating that situation.
2.1.2 Option B
This method is very alike to Option A, but it is less flexible, since I have integrated the UserProfile credentials into the Person entity type. By proceeding this way, you would force every Person to have a LoginEmail and a Password.
Conversely, Option A permits you to retain Persons (or People) that are not related to the User context in a clean and separate manner, without employing NULLable (i.e., optional) properties.
2.1.3 Option C
The diagram displayed as Option C describes the statements that follow:

A Person may log in via zero-or-one UserProfile
A UserProfile holds zero-one-or-many entries in UsernameHistory

This method implies notions pertaining to the subject known as temporal databases. You may find my take on Scenario A and my response to Scenario B of relevance in this respect.
With this approach —once the schema depicted in this diagram is built on a certain SQL environment— you could retain the “active” Username in a, say, UserProfile table and all the “past versions” in a, say, UsernameHistory table.
As per the data manipulation factor, basically, every time that a UserProfile UPDATEs his or her Username, you save a copy of the last Username version in the UsernameHistory table. Each History Version is uniquely identified by a combination of the Date and Time datum in which such row was modified and, or course, the UserId FK that links the row in question with its respective UserProfile.
As in any relational database project, you should seriously consider using ACID TRANSACTIONS in order to guard your data integrity and consistency.
2.1.3 Option D
Let us suppose that, in your domain of discourse of interest, a Person can perform two roles: Administrator and Author. This is where Option D comes into play, and the next assertions are useful in delineating our informational diagram:

A Person may be zero-or-one Administrator
A Person may be zero-or-one Author
A Person may login via zero-or-one UserProfile

In this way, a Person holds his or her particular “active” Usernames in his or her specific UserProfile and, also, can possess a Handle in case said Person is an Administrator and another different Handle when carrying out the role of Author.
3. Possibility with the common ASP.NET Users and Roles approach
You mentioned via comments that you are using ASP.NET MVC as your application development platform and, although I am not sure if you are as well utilizing the common (or default) ASP.NET course of action for handling Users and Roles, this section can serve as a good reference assuming you have to deal with the typical conditions.
3.1 ASP.NET Users and Roles approach diagram
The approaches suggested in Figure 3 appear feasible since you would not need to modify the tables that are usually created in ASP.NET apps. As you can see, you would only have to create an additional table for retaining the Handles (or Nicknames, or Aliases, if you prefer) that a given User may (or may not) have in regard to a certain Role assigned to such User in your application program. In other words, the same User could hold multiple Handles, and the context for each Handle instance would be an exact Role performed by the User at issue.
Attaching a Handle column directly to the UsersInRoles table could be possible too, but it may have unfavorable consequences in the ASP.NET authentication and authorization processes, aspects that, as you know, are  of the greatest importance for the security of any app, so this method would require especial research and testing.

3.1.1 Option A
This method depicts a Handle table that is connected to the UsersInRoles table by virtue of a composite FK that consists of the UserId and the RoleId columns. Said Handle table has an AK set up for, precisely, the Handle column, which implies that a specific handle value* could only be INSERTed in one single row.
3.1.2 Option B
In this option I have portrayed a Handle table that also makes a reference to the UsersInRoles table by means of a FK composed of the UserId and the RoleId columns, but it shows a slightly different approach that displays an AK constituted by the RoleId and the Handle columns, which would open the chance to have the same handle value repeated in multiple rows, but only in combination with disctinct RoleId values.

Notes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3 An ALTERNATE KEY is a property (or a combination of properties) that holds values that uniquely identify an entity type occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYs. In an IDEF1X diagram, they are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually declared in a logical-level SQL DDL design via (i) a single- or multi-column UNIQUE constraint along with (ii) one or more NOT NULL constraints.


Huge cross product view query speed I have a huge view what consists of about 15+ million rows and about 150+ columns.
That view is made of a lot of LEFT OUTER JOIN between a master table and various related tables.

That master table contains a list of products with a unique product id, but the view is a cross product of the products and all their attributes contained in the external tables.

We have an application that needs to materialize that view applying some business logic to the dataset. What the application does is go through each product, collect the rows from that view and retrieve the attributes of each column through a SELECT DISTINCT query and then, after everything is computed, the result gets written back to a row in a new table.

Of course this is a very slow operation because we run a SELECT DISTINCT for each column, multiplied by each row of the view.

Is there any other way to manage such a huge view? I've been thinking about splitting it up by a certain number of rows or by columns, but I'm not even sure that would make any difference.
Are there best practices to work around this issue?
Thanks!

It'll sound a little stupid, but creating a separate composite non clustered index on the attributes table on product id and each column, resulting in 150+ indexes on the attributes table, will get you the ultimate speed for that query because it will support the select distinct column value by product from the attributes table. Your insert performance will be super painful.


Whether Cold backup method can be used to “migrate oracle DB”? I have to migrate one database.

I have exact server replica. (Same directory structure on target as well as source database)

Previously I have performed DB migration using export/import datapump (As we don't have access to any tools).
I want to ask : Whether I can create replica using Cold backup method ?

DB Details :
Oracle 10.2.0.3 (On Both Source & target)

DB is currently running in no archive mode.

Steps I am going to follow :

create pfile from spfile
shutdown normal on source db
Copy pfile to target server $ORACLE_HOME/dbs
Copy all the dbf , ctl , rdo files to target server (Using binary mode FTP) (select name from v$datafile; select member from v$logfile; select name from v$controlfile)
startup pfile='<path>' on target server

Doubts :

Can cold backup can be used to create replica/ DB migration ?

If I tried above process , shall I am able to start the db on target server without any errors ?

As I don't have any archive logs , will that create any problem here ?

EDIT

This has been done perfectly fine with no oracle errors.

Few points :

Even you can change the directory of the dbf file while using this method. All u need to do is :

startup mount pfile='pfilepath'

alter database rename file '/flowsystem/oracle/SYSTEM01.DBF' to '/flowsystem/oracle/oradata/SYSTEM01.DBF';

shutdown

startup

If you have done changes in directory structure of controlfile , log file then edit the pfile before startup & then startup using pfile (Not Tried)

Information Source : http://www.adp-gmbh.ch/ora/admin/backup_recovery/cold_backup.html

Should work when the directory structure is the same. Check you $ORACLE_HOME/dbs directory to see if you have other SID related files. If your database does not need recovery then no archivelogs are needed. Do not forget to change at least the DBID if you need to integrate the database in the RMAN backup process.

If you use RMAN for backups then you can also clone your database to another server. This way you can place the files in a different location and the DBID (and DBNAME) will be changed during the cloning.


Why WordPress use a column prefix? I'm trying to learn some basic database design through open source projects.

And look into the WordPress database schema, the posts table for example, you'll find something like this

post_author
post_date
post_title
post_content
...
...
etc.

We're already in the posts table, why not just author, date, title, content? Is there any reason? Or just because WordPress is too old..?

(The same in other tables. In users table, will find user_login, user_pass, user_email, etc.)

I found many people say that using a column prefix is a bad practice, so, just title, content... will be a better design?

There is more than one school of thought concerning the naming of columns or fields.

One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found.  Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, but that it's the date of a post or a comment.

One advantage of this convention is that it makes it easier to maintain a data dictionary, where the full definition of each data element is to be found.  It's easy to navigate from a column name to the corresponding data dictionary.

This convention was apparently used in Wordpress.

Another school of thought says that the name of a column or field ought to convey the intent of the column or field in the table or record where it is found,  according to the role it plays in that context.  Thus,  date merely tells you that it's a date,  but post.date presumably tells you that it's the date of a post.  And comment.date would tell you that it's the date of a comment.

There are advantages and drawbacks to both schools of thought.  The most important thing is to be consistent, once a naming standard has been agreed to.  A naming convention that is sometimes disregarded creates pitfalls for the maintainer.

One drawback to the second convention is that FKs can generally never have the same name as the PKs to which they refer.  Another drawback is that, if there is a context, such as the forms manager,  in which "date" is a reserved word,  now you're forced to violate your own convention.

One drawback to the first convention is that it makes it awkward to express a reflexive relationship, one in which an FK refers to a different row in the same table.  Take, for example, "supervisor_id", a column you find all the time in employee tables.   You can't give it the same name as the PK that it refers to, because then you would have two columns with the same name in the same table, and that's forbidden.  So now you're forced to violate your convention.


Is the function PARSENAME() the opposite of QUOTENAME() In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author McNets had noticed, that some internal functions could handle quoted (e.g. [MyTable]) and unquoted (e.g. MyTable) parameter values passed on (e.g. OBJECT_ID()).

In the answer (1) given by David Browne - Microsoft the quote was made that:

  ...the inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.

I disagreed with this part of the answer with the following comment:

  I do agree that you can modify an argument passed to PARSENAME() to display the parts of a multi-part identifier be it the SERVER, DATABASE, SCHEMA or OBJECT part. But IMHO stating that PARSENAME is the opposite of QUOTENAME is a bit far-fetched.

My question now is:

Is the function PARSENAME() the opposite of QUOTENAME()?

Based on the comment by ypercubeᵀᴹ which reads:

  ...can you find a string x that parsename(quotename(x),1) will be different than x? If not, that falls into the definition of "inverse function" ;)  ...

... I created a simple statement to have a look at the different values returned by these functions. The code is as follows:

Code

DECLARE
-- Text that I will be converting using QUOTENAME()
@Text2Quote_1 AS NVARCHAR(20),
@Text2Quote_2 AS NVARCHAR(20),
@Text2Quote_3 AS NVARCHAR(20),
@Text2Quote_4 AS NVARCHAR(20),
-- The characters used for "quotation"
@QuoteChar_1 AS NCHAR(1),
@QuoteChar_2 AS NCHAR(1),
@QuoteChar_3 AS NCHAR(1),
-- The Parsing option as defined in the original MS documnenation for PARSSENAME()
@ParseParam AS INT

SET @Text2Quote_1 = N'Test'
SET @Text2Quote_2 = N'[Test]'
SET @Text2Quote_3 = N'Test.dbo.test'
SET @Text2Quote_4 = N'[Test].[dbo].[test]'

SET @QuoteChar_1 = ''''
SET @QuoteChar_2 = '['
SET @QuoteChar_3 = '"'

SET @ParseParam = 1 -- Parsing level : Object name
-- checking the results for ' single quotes
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_1) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_1) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_1) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_1) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_4

Basically, I set a string values, output that value, QUOTENAME() and output the value, then PARSENAME() the QUOTENAME()ed value and output it.

Results

The results were quite interesting:

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   '[Test]'                 'Test.dbo.test'          '[Test].[dbo].[test]'

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   NULL                     test'                    NULL

(1 row(s) affected)

When the base text Test.dbo.test (OriginalText_3) is passed on to the QUOTENAME() function it gets converted to: 'Test.dbo.test' (QuotedText_3)

When the QuotedText_3 string 'Test.dbo.test' is passed on to the PARSENAME() function it is converted to: test' (Parsed_QuotedText_3)

Conclusion

Seeing as I have proven ypercubeᵀᴹ's thesis wrong, I think it is safe to state that the function PARSENAME() is not the inverse of QUOTENAME().

Based on Aaron Bertrand's feedback here the code for square brackets [ ] (add to original script)

Additional Code for square brackets [ ]

-- checking the results for [ brackets
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_2) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_2) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_2) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_2) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_4

Output for square brackets [ ]

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
[Test]                   [[Test]]]                [Test.dbo.test]          [[Test]].[dbo]].[test]]]

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

and to complete things the code for the double quotes

Additional code for double quotes "

-- checking the results for [ brackets
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_3) AS QuotedText_1,
        QUOTENAME(@Text2Quote_2,@QuoteChar_3) AS QuotedText_2,
        QUOTENAME(@Text2Quote_3,@QuoteChar_3) AS QuotedText_3,
        QUOTENAME(@Text2Quote_4,@QuoteChar_3) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_4

Output for double quotes "

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
"Test"                   "[Test]"                 "Test.dbo.test"          "[Test].[dbo].[test]"

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

Based on ypercube™'s feedback and Aaron Bertrand's new feedback we have come to a mutual conclusion

PARSENAME() is the inverse of QUOTENAME() if the following conditions are met:

PARSENAME(x,1)-1(QUOTENAME(x,q)) where q={1,2} and x='{'any_string_value'}

References:

PARSENAME (Transact SQL) (Microsoft Docs)
QUOTENAME (Transact SQL) (Microsoft Docs)


Resolving Queries into a List of `object name.field` I work on a team that has an SSRS setup with roughly ~1300 (and growing) canned reports. As one can imagine, this has presented problems when a breaking change is introduced to a table. Finding all the reports that may touch a table/field is error prone at best.

I'm trying to programmatically build a dependency model. Getting the schema for Owner -> Object -> Fields and how views/tables relate is fairly trivial.

What I'm struggling with is how to get a resolved list of fields from a given query. I can pull the query from the RDL files, but interpreting those files is far less so. Queries can contain aliases for fields and tables alike, plus there are problems such as select *. I'm trying to avoid a regexp hack, and I really don't want to write a SQL interpreter...

My initial thought was to loop through the RDL files and parse the explain plan output. While technically possible, this doesn't give me a complete list of resolved fields.

Is there any method in which I can use to get the DB analyse a given query and return a list of Object_Name.Field_Name? I don't mind having to do some text processing to pull out the results if necessary.

Oracle 12c provides the procedure DBMS_UTILITY.EXPAND_SQL_TEXT.

EXPAND_SQL_TEXT Procedure

If you have a query, it can expand its text to involve th base tables and its columns.

Example:

SQL> create table t1 (C1 number, c2 number);

Table created.

SQL> create view v1 as select * from t1;

View created.

SQL> create or replace view v1 as select c1 as c3, c2 as c4 from t1;

View created.

SQL> create or replace view v2 as select * from t1;

View created.

To find the tables and columns used for select * from v2:

SET SERVEROUTPUT ON
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM v2',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

Output is:

SELECT "A1"."C1" "C1","A1"."C2" "C2" FROM  (SELECT "A2"."C1" "C1","A2"."C2" "C2"
FROM SYS."T1" "A2") "A1"

The rest is up to you though.


Is it possible to filter the list of databases in the Object Explorer of the SSMS? I know how to filter the list of tables - using the filter icon. However, this icon is disabled when the Databases node is selected.

Still, I am wondering whether it is possible.

This is now possible if you are using SQL Server Management Studio 2016. You can filter databases by Name, Owner and Creation Date.

I'm using SSMS build number 13.0.15900.1 - you can download a stand-alone version from Microsoft or just use this link: https://msdn.microsoft.com/en-us/library/mt238290.aspx


How do you get the explain plan? Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.


Is there a way to set an order to the way files are available in Azure? I want the latest backup first I use Azure to store backups, the backups are saved in Azure.

this is how I am doing my backups and saving to azure:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master
-Q "EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'SQLAG',
@URL = 'https://MYCOMPANYbackups.blob.core.windows.net/SQLMAINSERVER-datafiles-full',
@Credential = 'SQLAzureCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@LogToTable = 'Y'" -b

This is working fine.

There are some points I would like\need to improve though. When I need to restore a backup from Azure the backups are stored in the order they have been saved, and generally I need the most recent backup.

For example the picture below (apologies I have used this thick green to hide sensitive information like server, accounts and database names, it got really ugly but just to have an idea):

these are differential  backups:

As I often need the latest backup of an specific database(s) is there a way to order how the files are stored in azure so that the first one available is the latest saved?

conclusion

After downloading and installing Microsoft Azure Storage Exporer
I can order the backup files as you can see on the picture below.

You can't change how the files are stored in Azure but I guess you don't want to, you just want so change the sort order of the view in the Azure portal?

In that case I recommend using Microsoft Azure Storage Explorer instead.


Joining multiple tables results in duplicate rows I am getting more rows then I expect returned by my query.

I believe it has something to do with my join statements.

There are multiple tables that have different information in them.  Person holds the primary information about the person but not the address, or phone, or email.   This is because the original designer wanted the table to be able to hold multiple phone numbers and emails and addresses.

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    ,ISNULL(Person.isClient, '')
    ,ISNULL(Person.UDF1, '')
    ,ISNULL(Address.City, '')
    ,ISNULL(Address.state, '')
    ,PersonAddress.Person
    ,PersonAddress.Address
    ,ISNULL(Phone.PhoneNumber, 'N/A')
    ,Email.Email
    ,Person.Website
FROM Person
    left join PersonAddress on Person.ID = PersonAddress.Person
    left join Address on PersonAddress.Address = Address.ID
    left join PersonPhone on Person.ID = PersonPhone.Person
    left join Phone on PersonPhone.Person = Phone.ID
    left join Email with (nolock) on Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and Address is not null
    and name like '%Mike%'
ORDER BY isClient asc;

For this example, I get 6 rows of "Mike Worths". 3 of the copies have one email and three have another email.

For "Mike Pamstein" I get two duplicate rows with the same email.

I need the results to only contain a single unique row for each person.

I want to drop the second email.

Presumably, you want to see a single entry for each unique person/address/email/website combination.  If so, try this:

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].[state], '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
FROM dbo.Person
    LEFT JOIN dbo.PersonAddress ON Person.ID = PersonAddress.Person
    LEFT JOIN dbo.[Address] ON PersonAddress.[Address] = [Address].ID
    LEFT JOIN dbo.PersonPhone ON Person.ID = PersonPhone.Person
    LEFT JOIN dbo.Phone ON PersonPhone.Person = Phone.ID
    LEFT JOIN dbo.Email WITH (NOLOCK) ON Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and [Address] is not null
    and name like '%Mike%'
GROUP BY (person.FirstName + ' ' + person.LastName)
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].state, '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
ORDER BY isClient asc;

The GROUP BY clause at the end ensures only a single row is returned for each unique combination of columns in the GROUP BY clause.  This should prevent duplicate rows being displayed in your results.

A couple of things to note:

Always use the schema qualifier on the FROM clause.  FROM Person should be FROM dbo.Person -> this eliminates any confusion if you introduce new schemas in the future, and prevents the query optimizer from having to look for the default schema for your user.
For maintainability in the future, you probably want to name columns the same regardless of which table they are in.  So for instance, instead of the ID column in the People table being named ID, and it being named Person in the Address table, I'd name it PersonID in both tables.  This prevents confusion (read bugs) in joins such as dbo.Person LEFT JOIN dbo.Address ON Person.ID = Address.Person.
Instead of naming tables like Person, they should be named after the collection of items they contain, in plural.  So, Person becomes People, and Address becomes Addresses.  This eliminates confusion -> does the Address table actually contain a single address or multiple addresses?
WITH (NOLOCK) should be avoided like the plague it is unless you fully understand the consequences of reading rows that have been modified by other transactions but not yet committed.  From MSDN:

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

  In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

    The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

    The SNAPSHOT isolation level.


Is there a tool that can check the availability of an Oracle 11g database on a remote machine? I'm configuring the boot sequence on a Linux box, and one of the startup applications is dependent upon an Oracle instance being available on a different box. I would like a bash scriptable tool to check the availability of the Oracle instance. Likely, the power button on both boxes will have been hit within seconds of each other, and I need the longer Oracle process to finish before my application attempts to connect.

The only way to be sure that the remote database is up and that the listener is up and that the database is registered with the listener properly would be to actually make a connection.  You could use the SQL*Plus utility (assuming the Oracle client is installed on the linux box your application runs on) to attempt to make a connection.  Something like

Create a file check_db_up.sql

whenever sqlerror exit 1;
connect username/password@<<TNS alias>>
select 1 from dual;
exit 0;

Then invoke this script in your bash shell script and look at the return code

sqlplus /nolog @check_db_up.sql

If that returns 1, there was an error and the database isn't up.  If it returns a 0, the database is up and accepting connections.


Installing Oracle Forms and Reports on a 64bit machine I'm trying to install Oracle Forms and Reports on a new server 64 bit (Ubuntu Trusty Tahr).  (I'm only actually interested in the report server component).

I can't find a version that I can install on oracle.com - the only versions I can find require Oracle Fusion Middleware which we are not licensed for. (I have actually tried to install this but I can't get to work - didn't follow this up too far due to the licensing issues)

My fallback plan is to install the 11g 32 bit version in a 32 bit chroot on the server, but am hoping to avoid this.

Does any one know if it's possible to get the reports server running without Middleware? (Or any ideas on which download from Oracle.com would be a likely candidate).

Thanks

edit: just to clarify - the answer given by Vérace didn't answer the question (the versions suggested still required Middleware to be installed). I'm still wanting to determine if it is possible to run a 64bit version of Reports Server without Middleware already being installed

This couldn't have been easier

Forms and Reports here (Fusion Middleware) and alternative (non-Fusion) here. You may need an app server - not sure.

This was a relatively easy Google - that normally merits a -1, but since it's your first post here, I'll let it slide. Do they not have similar rules about question research on StackOverflow (where you have a rep. of nearly 1.5k)?


Sleep Processes are Running Longer Than Allowed From time to time my mysql server will run into a "too many connections" error.  I believe part of the problem is large amount of sleep processes that are running, some over 1000 seconds.

When looking into the issue, I saw this and investigated.  I checked my timeout values with

show variables like "%timeout";

And both interactive_timeout and wait_timeout are set to 600.

How/Why would these sleep processes be running so long, if they're supposed to stop at 600?

There are two possibilities the come to mind:

the values of interactive_timeout and wait_timeout were not 600 when those threads first connected, so they are using the values that existed when they first connected, or
the application is changing the timeout values for its sessions after connecting.

If you're running out of available connections, though, why not just raise max_connections?   If the threads are sleeping, you're not talking about a significant resource cost.


Handle expired SQL login's passwords in SQL Server Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming?
Many times during working hours I got this message from a production server.
I have a 'sa' level privilege in SQL Server.

Working Environment

Microsoft SQL Server 2012 Express
Windows Server 2012 R2
VMware,Inc. VMware virtual platform

Complementing to Julien's answer, you can be proactive to schedule below tsql as a job to alert you on daily basis especially on DaysUntilExpiration of LOGINPROPERTY e.g. get all the logins from your database server that are going to expire in 2 days.

SELECT name as LoginName,
create_date as LoginCreateDate,
modify_date as LoginModifiedDate,
is_policy_checked ,
is_expiration_checked ,
-- returns the number of days until the password expires.
LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration,
LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,
LOGINPROPERTY(name, 'IsExpired') IsExpired,
LOGINPROPERTY(name, 'IsMustChange') IsMustChange
From sys.sql_logins
--- optional filter
-- where LOGINPROPERTY(name, 'DaysUntilExpiration') >= 2


How to name tables such as "Suppliers Categories" I am refactoring a very old DB (< 2001) migrating from MyISAM to InnoDB and I experience some issues with the CamelCase notation. I don't know why but with the use of MySQL Workbench, sometime, my schemas are renamed in lowercase.

Anyway, the tables names in this DB are sometime pretty odd so I would like to rename them. As I am using the Illuminate\Eloquent ORM, it would be pretty convenient to use plurals for tables.

So I am proposing these kind of changes:

+-------------------+--------------------------+
| Before            | After                    |
+-------------------+--------------------------+
| Retailer          | suppliers                |
| RetailerComment   | suppliers_comments       |
| Category2Retailer | suppliers_has_categories |
+-------------------+--------------------------+

The question is:

Is it correct (acceptable), to name the table that holds the comments made on suppliers: suppliers_comments, both plurals? I could have named the table simply comments, but then it would have interfered with other tables.

I know this question could be primary opinion based, but I am rather looking for the largest consensus using as base of thoughts:

MySQL - Identifier-case-sensitivity
Larvel - Naming Conventions

Any objective advice? I am not a MySQL/DB expert...

Is it correct (acceptable), to name the table that holds the comments
  made on suppliers: suppliers_comments, both plurals? I could have
  named the table simply comments, but then it would have interfered
  with other tables.

You can, But before RENAME of any tables name stop here & see the MySQL blog documentation here Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table.

MySQL RENAME TABLE examples

First, we create a new database named hr that consists of two tables: employees and departments.

To create the hr database

CREATE DATABASE IF NOT EXISTS hr;

To create departments & employees tables

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    id int AUTO_INCREMENT primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    department_id int not null,
    FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

Second, we insert sample data into both employees and departments tables:

INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');

INSERT INTO employees(first_name,last_name,department_id)
VALUES('John','Doe',1),
 ('Bush','Lily',2),
 ('David','Dave',3),
 ('Mary','Jane',4),
 ('Jonatha','Josh',5),
 ('Mateo','More',1);

Third, we review our data in the departments and employees tables:

SELECT
    department_id, dept_name
FROM
    departments;

SELECT
    id, first_name, last_name, department_id
FROM
    employees;

Renaming a table referenced by a view

If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.

For example, we create a view named v_employee_info based on the employees and departments tables as follows:

CREATE VIEW v_employee_info as
    SELECT
        id, first_name, last_name, dept_name
    from
        employees
            inner join
        departments USING (department_id);

The views use the inner join clause to join departments and employees tables.

The following SELECT statement returns all data from the v_employee_info view.

SELECT
    *
FROM
    v_employee_info;

Now we rename the employees to people table and query data from the v_employee_info view again.

RENAME TABLE employees TO people;

SELECT
    *
FROM
    v_employee_info;

MySQL returns the following error message:

  Error Code: 1356. View 'hr.v_employee_info' references invalid
  table(s) or  column(s) or function(s) or definer/invoker of view lack
  rights to use them

We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:

CHECK TABLE v_employee_info;

We need to manually change the v_employee_info view so that it refers to the people table instead of the employees table.

Renaming a table that referenced by a stored procedure

In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.

Note: Here I have taken all codes & pictures from the mysqltutorial.org to clarify the RENAME of table.

After edit about the Naming Conventions for MySQL

Case sensitivity is set when a server is installed. By default, the
names of database objects are case sensitive on UNIX and not case
sensitive on Windows. For example, the names CUSTOMER and Customer
are different on a case-sensitive server.
Database names must be unique. For each user within a database, names
of database objects must be unique across all users. For example, if
a database contains a department table that User A created, no other
user can create a department table in the same database.
A name cannot be a reserved word in MySQL unless you enclose the name
in quotation marks. <a
href="https://dev.mysql.com/doc/refman/5.7/en/identifiers.html" rel="nofollow noreferrer">See
the MySQL documentation for more information about reserved words.

As Mr. Max Vernon already said that here Database names are completely up to the person creating them. Having said that, it makes sense to use some kind of sane naming convention.

For further your ref here


A question about multisite Always On design Your advice on Always On  design please...

Imagine a scenario where you have 2 datacentres in different countries. You want to be able to run at full capacity in either datacentre. in each datacentre you wish to offload backup and read-only queries to a readable secondary when that datacentre is active.

In an ideal world you would like to have 4 servers in total, two in datacentre 'A' and two in Datacentre 'B', you wish to reduce the amount of traffic across the wan link so would prefer to limit the replication traffic to one stream of asynchronous traffic. You also want to have automatic fail-over in the active datacentre wherever that might be. In effect, you would like a Windows cluster with all 4 servers in it but have it subdivided by groups into two separate groups, one per site, running synchronous commit within the site yet asynchronous commit between the sites due to WAN latency.

Can a readable secondary on the second site act as the source for synchronous commit to anther node on that site?

If all replication has to stem from the primary then I suppose I am left with writing a PowerShell script to set Availability and fail-over modes for the available replicas in the event of a fail-over between sites.

Is there an easy solution that I'm missing?

Cheers

In traditional Always On Availability Groups, all replication traffic comes from the primary replica. If you have two replicas in a remote data center, they will both be updated from the primary replica, period. You can't do primary-to-secondary-to-secondary.

SQL Server 2016 introduced Distributed Availability Groups, which might satisfy your design goal. However, this is going to be much more complex, much less documented, and I wouldn't recommend doing this for your first AG.


I noticed that my MySQL slave's DB size is smaller than the Master's DB I have a very simple setup of a master MySQL DB with one slave MySQL DB replicating from it.

Initially, when setting up the slave, I used mysqldump to get an initial DB dump from the master like this :

mysqldump --single-transaction --master-data  some_db > someSQL.sql

Then I go to my slave DB, created a brand new DB named "some_db" and then tried to reverse dump:

 mysql -uroot --one-database some_db < someSQL.sql

But then I did a comparison of the DB sizes on the MySQL prompt and noticed that the master's some_db's size is around 7.6 gigs, but that of the slave's is only 6.4gigs.

I find this is concerning because I am wondering if certain data got lost. From what I understand, the DB does not contain blobs or any triggers, so where did that missing 1.2 gigs probably go?

Please let me know what your thoughts are.

Thanks
IS

As tables go through many INSERT/UPDATE/DELETE cycle, mysql doesn't reclaim the space from deleted rows; instead, it internally flags the row as 'deleted' and leaves it in the tablespace. 1G size difference is not uncommon and shouldn't cause alarm.

You can try and run optimize table command on master and verify the table size get reduced. It is a good practice to routinely optimize table that does a lot of DELETE operation.


Which columns need index in recursive table and soft delete I have a Category table like below :

I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI dropdown it takes about 3 minutes.

Which columns need an index to improve query speed?

Table definition:

 CREATE TABLE [dbo].[Category](
    [CategoryId] [uniqueidentifier] NOT NULL,
    [CategoryCode] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](400) NOT NULL,
    [ParentId] [uniqueidentifier] NULL,
    [DisplayOrder] [int] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [IsActive] [bit] NOT NULL,
    [IsShowOnMenu] [bit] NOT NULL,
    [AttachmentId] [uniqueidentifier] NULL,
    [Depth] [int] NOT NULL,
    [Path] [nvarchar](max) NULL,
    [IsDeleted] [bit] NOT NULL,

 CONSTRAINT [PK_dbo.Category] PRIMARY KEY CLUSTERED
(
    [CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

My query is in C#. I used C# statment to generate show all category in dropdown.
My query is:

exec sp_executesql N'SELECT
[Project1].[DisplayOrder] AS [DisplayOrder],
[Project1].[CategoryId] AS [CategoryId],
[Project1].[CategoryName] AS [CategoryName]
FROM ( SELECT
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[CategoryName] AS [CategoryName],
    [Extent1].[DisplayOrder] AS [DisplayOrder]
    FROM [dbo].[Category] AS [Extent1]
    WHERE (([Extent1].[IsDeleted] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND ([Extent1].[ParentId] = @p__linq__0)
)  AS [Project1]
ORDER BY [Project1].[DisplayOrder] ASC',N'@DynamicFilterParam_1 bit,@DynamicFilterParam_2 bit,@p__linq__0 uniqueidentifier',@DynamicFilterParam_1=0,@DynamicFilterParam_2=NULL,@p__linq__0='8d8dd739-a132-e3ef-5443-159ecf8adc44'

Above query runs for each item, because I need to get all children.

Try this index for some noticeable improvement:

CREATE NONCLUSTERED INDEX [CatIndex1] ON [dbo].[Category]
(
    [ParentID] ASC
)
INCLUDE (   [CategoryID],
    [CategoryName],
    [DisplayORder])

I am interested in the time reduction. Please let me know your findings.

p.s. consider sorting only once with the ORDER BY if you're running it multiple times. Perhaps put data into a C# DataTable or SQL temp table and sort at the very end of all retrieval operations.


Multicolumn GIN index not fully facilitated by query planner I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.
After defining an index on two columns (a,b) of a large table and executing a query involving conditions for both columns a = 4 and b @> '{"key": "value"}'::jsonb, the planner applies the multicolumn index only for one of the two conditions. It seems that another scan is executed in parallel for the other condition, using another index. Why can this happen?
A few more details:
The index is:
CREATE INDEX coupons_campaignid_attrs_index ON coupons USING gin (campaignid,attributes);

The query is:
explain (ANALYZE, COSTS)
SELECT COUNT(*)
FROM coupons
WHERE campaignid = 97 AND attributes @> '{"CountryId": 3}';

The plan is:
Aggregate  (cost=34532.02..34532.03 rows=1 width=8) (actual time=56613.161..56613.164 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=32208.05..34526.31 rows=2284 width=0) (actual time=23541.180..56384.603 rows=1090012 loops=1)
"        Recheck Cond: ((attributes @> '{""CountryId"": 3}'::jsonb) AND (campaignid = 97))"
        Rows Removed by Index Recheck: 10532325
        Heap Blocks: exact=138539 lossy=583394
        ->  BitmapAnd  (cost=32208.05..32208.05 rows=2284 width=0) (actual time=23478.234..23478.236 rows=0 loops=1)
              ->  Bitmap Index Scan on coupons_campaignid_attrs_index  (cost=0.00..1543.35 rows=179379 width=0) (actual time=14247.668..14247.668 rows=81803572 loops=1)
"                    Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
              ->  Bitmap Index Scan on campaignid_deleted_idx  (cost=0.00..30663.31 rows=2284099 width=0) (actual time=8658.669..8658.670 rows=3097095 loops=1)
                    Index Cond: (campaignid = 97)
Planning Time: 1.711 ms
Execution Time: 56619.880 ms

EDIT: I removed the second index campaignid_attrs_idx to see if then the multicolumn index will be used for both conditions. Strangely I still see that the only one of the conditions used in the index scan. Here's the plan:
Aggregate  (cost=181951.27..181951.28 rows=1 width=8) (actual time=209633.017..209633.018 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=1424.30..181945.81 rows=2183 width=0) (actual time=8938.605..209401.433 rows=1091580 loops=1)
"        Recheck Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
        Rows Removed by Index Recheck: 31487517
        Filter: (campaignid = 97)
        Rows Removed by Filter: 80674951
        Heap Blocks: exact=121875 lossy=5572599
        ->  Bitmap Index Scan on coupons_campaignid_attributes_idx  (cost=0.00..1423.75 rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589 loops=1)
"              Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
Planning Time: 6.885 ms
Execution Time: 209638.234 ms

I cannot reproduce this issue where it chooses to use two indexes, but I suspect it has something to do with the massively incorrect estimate for JSON containment condition; combined with another unknown factor like maybe some kind of index bloat.

...rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589

That is off by over 400 fold.  But there isn't much you can do about it, other than pull "CountryId" out into its own column, or upgrade to v13 where json containment selectivity estimation has been improved (though not by as much as if it were in its own column).
In any event, the choice of two different indexes is unlikely to actually be a problem.  When you use a GIN multicolumn index like this, it behaves pretty much the same as two different indexes combined with a BitmapAnd.  It is just that the BitmapAnd is occurring inside a black box where EXPLAIN can't show you the details.  I would generally not expect much difference in performance.  (One place I have seen a difference is if the tuples which match one condition are physically separated from the ones which match the other, i.e. all the tuples with campaignid = 97 are physically before all the tuples with attributes @> '{"CountryId": 3}' in the table.  The opaque BitmapAnd of the gin indexes has a optimization for this case that the transparent BitmapAnd does not seem to have.)
If your main goal is to make the query faster rather than either micromanaging exactly how to sublists get combined or redesigning your table, then I see two things to try.  One is increasing work_mem, as the massive number of lossy blocks can't be good for performance.  The other would be to create a multicolumn btree index on (campaignid, ((attributes->>'CountryId')::int)); and then rewrite the query accordingly:
WHERE campaignid = 97 AND (attributes->> 'CountryId')::integer=3


Incorrect uniqueidentifier works without a Primary Key but fails with one So we were recently running some logic tests on an insert into a table with a uniqueidentifier as a primary key. Here's my test query;

IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #DatabaseTable
CREATE TABLE #DatabaseTable
        ([ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
        [LastUpdate] [datetime] NOT NULL,
        [Locked] [tinyint] NOT NULL)

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL  DROP TABLE #temptable
CREATE TABLE #temptable (ID varchar(36), Data nvarchar(max))

INSERT INTO #temptable (ID, Data)
VALUES ('g078f19e-e150-4bb9-b5f4-b20b3fc64016', 'text goes here')

SELECT tmp.ID ,tmp.Data
FROM #temptable tmp
LEFT JOIN #DatabaseTable db
    ON tmp.ID = db.ID
WHERE db.ID is null

This fails because the uniqueidentifier is not valid as it contains an incorrect value of 'g' that sits outside the acceptable range for this data type. This happened as a developer had altered it manually for testing as he knew that it wouldn't exist in the destination table but wasn't aware of the valid character list.

This is the error that it throws;

(1 row(s) affected)
Msg 8169, Level 16, State 2, Line 13
Conversion failed when converting from a character string to uniqueidentifier.

This is the behaviour that I would expect. However, when messing about in testing I removed the primary key on #DatabaseTable and it runs totally fine. Now I examined the execution plan expecting it to convert the uniqueidentifier to match the varchar(36), however I see this compute scalar;

CONVERT_IMPLICIT(uniqueidentifier,[tempdb].[dbo].[#temptable].[ID] as [tmp].[ID],0)

I can't seem to find any documentation from Microsoft on the actual syntax of CONVERT_IMPLICIT so I am assuming that the final zero is what it is replaced with if the convert fails, would this be a correct assumption (same as ISNULL/COALESCE)? Also, why does this work fine without the PK but fails with it?

I was able to repro this.

Both plans had the row from #temptable driving a nested loops join.

When #DatabaseTable has a primary key there is an index to seek into so the inside of the join has a seek. It is necessary to cast the varchar to uniqueidentifier to get the value to seek at which point the attempt to convert the invalid value fails.

When there is no primary key the plan has a scan on the inside of the nested loops instead. it can defer the evaluation until it has a row to compare, there is no row output from the scan on #DatabaseTable (as it is empty) so the CONVERT_IMPLICIT is never required to be evaluated.

The position of the compute scalar may lead you to believe otherwise but see Compute Scalars, Expressions and Execution Plan Performance for more about that.


Determining the tables and impact of a large number of SPs I'm trying to track down what is causing some response times to increase when data is compressed by page compression, when the system is not CPU bound and 95% of responses see a decrease in response time.

So what I did is track it down to a single process happening in the system, and I profiled that process to determine what SPs and UFNs are fired off during the process. My original thought was that I could run each SP and UFN in isolation and take a look at the query plans to see where full scans are happening, this might require the data to be unpacked from the compression and might cause a wait to fire.

So what I have right now is:

A list of SPs with the parameters used
The database that the SPs are run on
Unfettered administrator access to the system to reproduce the issue
A profile trace of the process in question

Since there's something like 35 SPs/UFNs I'd have to sort through, I'm wondering what the most efficient way is to narrow down the cause. I can infer that some SPs are more likely culprits than others from my experience with the system, but I'd like to try to narrow it down in a more scientific way. Are there any tools or methodologies that might help me figure out the most likely offenders?

If I can determine the objects that are slower when compressed than when not, this is going to help inform our strategy around page compression.

A scientific approach is based on routinely collecting process and procedure performance information, either using a commercial package or a more home-grown solution. You could also start completely from scratch collecting information from Profiler or Extended Events. The important thing is to capture data regularly, and to make it easily consumable (e.g. using SSRS).

Usable historical information makes it easy to track gradual performance changes over time, anticipate increased resource requirements before they occur, diagnose sudden changes, and identify and test areas for improvement.

On that latter point, I believe your approach should be something like this:

Establish good baseline information over a reasonable period of time
Identify areas that might benefit from compression
Test both ROW andPAGE compression in the improvement target area
Test aggregate changes on a representative workload
Refine or regress changes that were not beneficial
Implement the change and monitor the effects in production

This is a lot easier than attempting to track down performance regressions in complex code after the fact, without a baseline to compare with.


Calculation of occurrence of strings I have a table with 3 columns, id, name and vote. They're populated with many registers. I need that return the register with the best balance of votes. The votes types are 'yes' and 'no'.

Yes -> Plus 1
No - Minus 1

This column voteis a string column. I am using SQL SERVER.

It must return Ann for me

By using a case a statement you can turn your magic strings into numeric values. These can be summed and a group statement defines how to organize the summation.

Edit: I've added a sort as it is likely you are interested in the "winners".

SELECT
    SUM (
        CASE WHEN vote = 'Yes' THEN 1
        WHEN vote = 'No' THEN -1
        ELSE 0 END) BalancedVotes,
    Name
FROM
    myTable
GROUP BY
    name
ORDER BY
    BalancedVotes DESC


Connection reset to SQL Server when using SSH Tunnel I am trying to connect to an MS SQL Server database through DBeaver via an SSH tunnel but am getting the following error message when I "Test Connection...":

  I/O Error: Connection reset
        java.net.SocketException : Connection reset

If I set the Keep-Alive interval to anything other than 0, the error becomes the following:

Can't initialize tunnel
  Cannot establish tunnel
    com.jcraft.jsch.JSchException: timeout in waiting for rekeying process.

Looking at the debug log, ~/.dbeaver4/.metadata/dbeaver-debug.log, I see that the connection was initially successful but later reset. I don't believe it is a problem in my connection settings configuration (my colleages with Mac's connected successfully using the same settings)

Here's an excerpt from the dbeaver-debug.log:

2017-06-26 15:01:35.354 - Instantiate SSH tunnel
2017-06-26 15:01:35.355 - Connect to tunnel host
2017-06-26 15:01:35.356 - SSH INFO: Connecting to jdms.dssg.io port 22
2017-06-26 15:01:35.480 - SSH INFO: Connection established
.
.
.
2017-06-26 15:01:35.953 - SSH INFO: Authentication succeeded (publickey).
2017-06-26 15:01:36.083 - Connection failed (mssql-hashhashhash-hashhashhash)
2017-06-26 15:01:36.085 - SSH INFO: Disconnecting from jdms.dssg.io port 22
2017-06-26 15:01:36.086 - SSH INFO: Caught an exception, leaving main loop due to Socket closed
2017-06-26 15:01:36.135 - org.jkiss.dbeaver.model.exec.DBCConnectException: I/O Error: Connection reset
org.jkiss.dbeaver.model.exec.DBCConnectException: I/O Error: Connection reset
.
.
.
Caused by: java.sql.SQLException: I/O Error: Connection reset
.
.
.
Caused by: java.net.SocketException: Connection reset

Could this be a Linux specific issue?

How else can I debug my problem?

I am using DBeaver 4.0.8 to connect to a MS SQL Server database using the jTDS driver, in Ubuntu 16.04.

Try the JDBC driver or ODBC driver(Install/Config Info per platform) supplied by Microsoft for Linux clients.  I've found that I'm often more successful when using the drivers provided by the vendor than those that are included with the product (e.g. in this case DBeaver).  I would think all the other settings remain the same, just swap out the driver url to point the MS supplied driver instead.


Cannot recover Database from ibdata file My Server recently had a Kernel-Panic and I downloaded all the mysql data using the rescue mode.

Now when I upload the ibdata1 file and table folders to a local XAMPP Server and click on a table to view it's data(phpmyadmin), it says

#1932 - Table 'phpmyadmin.pma__table_uiprefs' doesn't exist in engine

My old Server had Plesk installed, maybe Plesk-MySQL has a different ibdata1 structure, compared to MySQL from XAMPP.

I hope anyone can help me to get my data back.

If you have access to MySQL other than thru PHPMyAdmin, you could try recreating the table. Practice this at your own risk thou.

create_tables.sql


Stored procedure and prepared statements I have 2 stored procedures:

CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
  FROM foodmaster d inner join user_details u
  on d.user_id=u.user_id WHERE (', param_CRITERIA ,') group by u.user_id;');
PREPARE stmt FROM @QUERY1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END IF;

CREATE PROCEDURE `sample`(param_CRITERIA VARCHAR(500))
BEGIN
SELECT user_name,count(*) as count FROM foodmaster d
  inner join user_details u on d.user_id=u.user_id
  WHERE ( param_CRITERIA ) group by u.user_id;
END;

I tried with same param_criteria in both SPs. In the first procedure, i am getting results. But not in second. Why?

Because the second one is, coincidentally, syntactically valid but not the correct way to do what you are doing.  You are trying to do dynamic sql without using dynamic sql.  What you are doing in #2 is actually putting the literal string ', param_CRITERIA ,' in the where clause, not the content of that variable.

The only way to do what you are trying to do is method #1.

I would also point out that if the value you're passing in method #1 is coming from an untrustworthy source (such as a web form or url url query string) then you are also potentially setting yourself up for an SQL injection vulnerability.

Edit:

You have updated your question so that example number 2 is no longer consistent with my answer, above, so for future clarity, I'll point out that your original query #2 looked like this:

... WHERE (,'param_CRITERIA',) group ...

Now you have removed the commas and the quotes:

... WHERE ( param_CRITERIA ) group ...

This does not work either, because, as I have pointed out, you are trying get the contents of a variable interpolated into the body of the query, blurring the boundary between logic and data.  The only way to do this is to craft a dynamic SQL query, which is what you're doing in #1.  Example #2 is not a dynamic query and cannot be made to be one.


Implications of changing compatibility mode in SQL Server from 100 to 110 I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012.

I was trying to execute below query to calculate percentile

select Distinct [KEY],PERCENTILE_CONT(0.25)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q1,PERCENTILE_CONT(0.50)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q2,
PERCENTILE_CONT(0.75)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q3,
PERCENTILE_CONT(1)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q4
from Mydatabase

but I am getting an error stating that

  Msg 10762, Level 15, State 1, Line 1
  The PERCENTILE_CONT function is not allowed in the current compatibility mode. It is only allowed in 110 mode or higher.

Can I change compatibility mode to 110?
What are the implications of changing compatibility mode from 100 to 110?

Please advice

Have a look at the following link:

ALTER DATABASE Compatibility Level

Scroll down and you will see the section "Differences Between Lower Compatibility Levels and Level 110" and identify if any of these items will affect you or not.  If not, then just change the level to 110.


Quality Control: Check file names in a folder against filenames in a table I have a records system in an Oracle database. The records system is used to retrieve pdf, jpeg, video documents, etc.

The design is quite simple: there is a table called infrastructure_records that has a column with hyperlinks to files:

+------------+------------------------------------------+
| project_id |                file_path                 |
+------------+------------------------------------------+
| Project 1  | X:\Records_System\Folder A\Project 1.pdf |
| Project 2  | X:\Records_System\Folder B\Project 2.jpg |
| Project 3  | X:\Records_System\Folder C\Project 3.mpg |
+------------+------------------------------------------+

We use a front-end that creates a clickable hyperlink out of the file paths. Using the hyperlink, users can navigate to the files.

Question:

I want to check the files on the network drive against the list of files in the database table.

If I could get a list of files from the network into a database table, then I could easily query for errors.

However, I'm not sure what the best way would be to create the list of files in an Oracle table.

There are approximately 60,000 files on the network drive, nestled in a variety of subfolders
The files don't exist on the same server as the Oracle database (I'm not an I.T. guy, so I'm pretty clueless about this sort of thing). However, I do have the drive mapped in Windows Explorer on the computer that I would be running the query from.
This QC operation would be run about once per month.
Performance isn't a huge concern. The list of files doesn't need to be "live", although that would certainly be a plus.
I can think of quick-and-dirty ways to do this using VBA scripting in MS Office or the like. But, it has occurred to me that there might be a more professional or out-of-box way to do this (perhaps using built-in Oracle functionality).

How can a I elegantly insert a list of files on a network drive into an Oracle table?

The following steps will need some tweaking, as you are working with multiple machines - your client machine, a file server (Windows?), a database server etc.  Thus, the paths and the file locations are just examples (which have been tested, though).  The steps A-B-C are performed only once.

Prerequisites / setup:

[A] Operating system: create a folder for storing the dir listings (in form of text files, see footnote 1) .

eg C:\Users\Wilson\datafiles

[B] Oracle: you need the permissions CREATE TABLE and CREATE ANY DIRECTORY  (ie read/write)

SQL> grant create any directory to wilson;
Grant succeeded.

[C] In Oracle, create a directory and an external table.

SQL> create directory external_tables as 'C:\Users\Wilson\datafiles';

Directory created.

create table xtable ( path_ varchar2(4000) )
organization external (
  type oracle_loader
  default directory external_tables
  access parameters
  (
    records delimited by newline
    fields terminated by ','
    missing field values are null
  )
  location
  (
    'dir_contents.txt'
  )
)
/

 Table XTABLE created.

Get the file list, and use the external table:

[1] Open a command line window.  Navigate to the network drive (X:).
    Execute the following command:

dir /s /b > C:\Users\Wilson\datafiles\dir_contents.txt

REM (this may take a minute ...)
REM Explanation:
REM  dir /s dives into every subdirectory (recursively),
REM  /b returns the dir listing without headers and summaries.
REM  > redirects the output into the file (absolute path provided)

Load the dir_contents.txt file into a text editor, make sure that it contains what you need.
If - as you wrote - Oracle runs on a different machine, you may have to upload it (via sftp).

[2] Use the external table (see footnote2).

SQL> select count(*) from xtable;
COUNT(*)
19362

SQL> select * from xtable fetch first 7 rows only;
PATH_
C:\Windows\System32\0409
C:\Windows\System32\12520437.cpx
C:\Windows\System32\12520850.cpx
C:\Windows\System32\3DAudio.ax
C:\Windows\System32\3DES.dll
C:\Windows\System32\8point1.wav
C:\Windows\System32\aaclient.dll

7 rows selected.

footnote1: This must be accessible when you are working with Oracle.  If you collect the datafile(s) on your client machine first, you must "upload" the files (eg via sftp) into the directory that you have specified when executing CREATE DIRECTORY ...

footnote2: The sample file contains the output of

C:\Windows\System32>dir /s /b > C:\Users\Wilson\datafiles\dir_contents.txt

(notice the paths and the command redirection).  If you are working with Linux, use "find / -type f > dir_contents.txt"


What is the most effective way to manage a community table with one customizable flag? Fair warning
I do not have dba training or anything of the like but I am managing a rather large web app database. I know the basics but I'm not sure how to handle this latest scenario.
Setup
I have about 300 different databases. 1 common database that all clients share for pulling static info and the remaining are 1 for each individual client.
Scenario
I have a table of about 130k items that never change. Not the largest table in the system but still a fair size. I have about 300 copies of this table currently. One for each client. They are all identical except for 1 simple enable/disable flag. I also have a single database where I store common information for all clients such as templates and fixed db lists that do not contain the enable/disable ability.
Question
Is their a way to store this mostly common db table in my "community" database and still allow individual "client" databases to have the enable disable flag?
My Thoughts
I know I could just do a simple [id,enable] table in the client db but that would add a join to the system. Then the question is: does the effect of the join on 130K entries worth the saved space? I just feel like I'm missing something obvious with this whole scenario.

No answers for months so here is the solutions I've come up with on my own.

I don't know if this is the best but if anyone else has this question it's worth a thought.

The common table can be stored in the common db as desired. Then each local database (which will always use a subset of the 130K possible values) can either store just the enabled values in their local copy of the table. Or store just the IDs of the enabled items. A disable would effective be a delete and an enable would be a create of either an ID or of the entire row.


how can database snapshot to be used to recover data? I know the logic behind database snapshot and how it works.
But my problem is how can database snapshot to be used to recover data?
can we take a backup of database snapshot and restore it in a new server?

I am using MS SQL server

Thank you.

how can database snapshot to be used to recover data?

Yes, you have to use restore database ... FROM DATABASE_SNAPSHOT. It is called reverting the database to a database snapshot. You would use it to reverse a serious error e.g. drop table, deleting data, etc.
This is important :

all changes made after the snapshot was created are lost.

.

can we take a backup of database snapshot and restore it in a new server?

No, you cannot. This is covered in Database Snapshot limitations

You cannot back up or restore database snapshots.

You cannot attach or detach database snapshots.

Very important :

Using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.


Concatenating Multiple Columns in TSQL I have the following data

JoinFlag    ReferenceType   ReferenceValue
A            CARRIER        Sample#######
A            LEASE_NUMBER       17438
A            LEASE_NUMBER       6054
A            STATION            ENERWST
B            LEASE_NUMBER       17704
B            LEASE_NUMBER       17711
B            STATION             RJ
B            STATION             C5

I would like to know how to write a SQL query in TSQL to get the following output

JoinFlag    ReferenceType   ReferenceValue
A            CARRIER        Sample#######
A            LEASE_NUMBER     17438, 6054
A            STATION          ENERWST
B            LEASE_NUMBER     17704, 17711
B            STATION          RJ, C5

The SQL should be generic.

The Query that I using is

SELECT [CustomTicketIgnoreList].JoinFlag,
   STUFF((SELECT ', ' + ThisTable.ReferenceType
            FROM   [CustomTicketIgnoreList] ThisTable
            WHERE  [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
            AND    ThisTable.ReferenceType <> ''
            FOR XML PATH ('')),1,1,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + ReferenceValue
            FROM   [CustomTicketIgnoreList] ThisTable
            WHERE  [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
            AND    ThisTable.ReferenceValue <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM [CustomTicketIgnoreList]
GROUP BY JoinFlag

Regards

Check it here: http://rextester.com/QQZWX40863

CREATE TABLE #t1 (JoinFlag char(1), ReferenceType varchar(20), ReferenceValue varchar(50));
INSERT INTO #t1 VALUES ('A','CARRIER','Sample#######');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','17438');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','6054');
INSERT INTO #t1 VALUES ('A','STATION','ENERWST');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17704');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17711');
INSERT INTO #t1 VALUES ('B','STATION','RJ');
INSERT INTO #t1 VALUES ('B','STATION','C5');

SELECT DISTINCT
    t2.JoinFlag,
    t2.ReferenceType ,
    (SELECT STUFF((SELECT ',' + ReferenceValue
            FROM #t1
            WHERE #t1.JoinFlag = t2.JoinFlag and #t1.ReferenceType = t2.ReferenceType
            FOR XML PATH('')) ,1,1,''))
FROM
   #t1 AS t2;


SQL Server Reporting Service 2014 installation Fail? I have an issue while trying to install SQL Server 2014 (Reporting Service only).

During the Installation process i get the following error and the installation ends with Failed to install the followings:

Reporting Service - Native

Error:

TITLE: Microsoft SQL Server 2014 Setup

The following error has occurred:

Error writing to file: Microsoft.SqlServer.Rmo.dll.   Verify that you have access to that directory.

Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

I have Administrative  privilege on the server with full control of all folders\paths.

Please if you have an answer for this ,share it with me !

Thanks
Osama Waly

Turns out that there was problem with the downloaded media. Tried another source and it worked fine.

Thanks