texts: Archival copy of table, including overwrote data

texts:
I have database server, with a database and table. The database and table can not be changed by me to ensure 3rd party support but the server configuration and MySQL configuration can be. On that table data is overwrote like so,

John Doe, red, car
Jane Doe, blue, boat

Incoming data John Doe, red, boat

results in

John Doe, red, boat
Jane Doe, blue, boat

A second database server, with a database and table that I can and need to change. Needs to contain a modifiable copy of the table but with the historical data. With the above example, it needs to contain,

John Doe, red, car
Jane Doe, blue, boat
John Doe, red, boat

Right now I have this working, but if you are wondering why I am posting this, it's because I don't like the current process. It's error prone. Right now I am using mysqlbinlog to read a binlog file from the 1st server in mixed mode and greping and awking and sed'ing the info I need and then changing the statements to UPDATE instead of REPLACE. Then I pipe that into MySQL. All on one line. This of course is far from ideal. No error checking, no daemon, no reconnection. If the source or destination server dies, all goes up in smoke.

Any ideas on a better method?


texts: What do the columns in SYS.USER$ represent?

texts:
In the SYS.USER$ view, what do the CTIME, PTIME, and LTIME columns represent?

Where can I find Oracle's documentation for this and other system views?


texts: PostgreSQL 9.6 pg_rewind - takes a long time to replicate

texts:
I use Streaming Replication,and Replication works normally.
Two servers are HA and PostgreSQL DB is replicated.
The data in the DB is about 40G.
When a failover occurs, the slave is successfully promoted to master, master becomes slave, and tries to replicate new master data.
But, It takes a long time to replicate data from the new master server.
(using pg_rewind / success / 38G - 6minutes).

Please let me know if there are other ways to save time.

This is the command I ran:

pg_rewind --target-pgdata="targetdir" --source-server="sourceserver"

This is the output:

connected to server
servers diverged at WAL position 35/DD0D2260 on timeline 37
rewinding from last common checkpoint at 35/DC4E94F8 on timeline 37
reading source file list
reading target file list
reading WAL in target
need to copy 39193 MB (total source directory size is 77268 MB)
698400/40134372 kB (1%) copied


texts: Is there a way to check when a user was mapped as DB Owner?

texts:
I have a user that was mapped to a database as DB Owner.

Is there a way to see when this mapping was granted?


texts:
Trying to rename a postgres database:

$ psql -d some_other_database
some_other_database=# ALTER DATABASE myapp_development RENAME TO myapp_development_old
some_other_database-# \l
                Name                |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
------------------------------------+---------+----------+-------------+-------------+---------------------
 myapp_development                  | rails   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

No feedback whatsoever after ALTER DATABASE...!  I am logged in as a Superuser and not aware of any current connections (but not sure how to be certain of that). What am I missing?

Edit: I erroneously transcribed my prompt in my original question so 2nd line had =# when, indeed (thanks Daniel), it should have had -#. Fixed above, now.


texts: What are those in the Data Collector folder ?  I tried to delete some and got the message that it was conflicting with some reference.


texts: SQL Server command line usage? Is it possible?

texts:
I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's "convenience" features are actually quite a hindrance.

Unfortunately, the place where I work is a MS shop, and is pretty stuck in their ways. I want to go back to the command line (I crave Vim!). I've installed a local subversion repository with a TortiseSVN front end, to replace the above mentioned "Solution Explorer" and have it running quite well. I have also installed Vim and while still working on customizing it, have it humming.

However, the one thing that I am yet to find a solution to is how to run commands to SQL Server via the command line. I'm used to MySQL/Postgres command line prompt since that is what I have used in the past and was using while going through my classes. However, I can't seem to find a way to execute my scripts via the command line in windows. I currently edit them in Vim, refresh the page in SSMS, and execute. I would prefer to not even touch SMSS unless needed.

I've seen posts suggesting sqlsharp, but when I went to the page, I get the feeling that isn't quite what I'm looking for.

--edit

I really wish that it was possible to have 2 answer check marks.  P. Campbell has the technical right answer, and I've tried it a few times (I keep getting an error when I try sqlcmd...  In researching that it's how named pipes is operating... Even though I have it set up and enabled, it doesn't seem to work.)

After trying that and not having any success, I tried the just copy and paste into Management studio method, but that was ridiculous... So I decided that maybe HardCode is correct too.


texts: is it possible to use utl_file_dir = '*' scope = spfile in 12c

texts:
Oracle 12c has deprecated  utl_file_dir = '*' scope = spfile,
but I need to use this functionality. Is there any option to do it?

Is it possible to give permission using Java classes?


texts: @OneToMany mapping but only one is needed

texts:
I have to create a new table to store version numbers of Terms&Conditions user have accepted and the time stamp of when they click accept. (This table only contains info related to user. Info about the T&C version itself is stored in another table)

That means user A could have accepted 3 versions of T&Cs that has been updated over time. The only version I care most about is the latest version the user has agreed to, but I still need to keep the history of accepted versions for integrity’s sake.

Scenario:

  I will already have the user entry in the database before the version records get stored.
  There can be users that has not agreed to any version of T&C.
  1 User may have accepted several version of T&C over time
  I want to retrieve only the latest version user has accepted along with the user object with minimal step taken.
  If possible when I delete the user I would like it if ALL accepted version records for that user also get deleted (foreign key on cascade delete or something)

How can I implement a clean solution for this? I have come up with two possibilities but I’m not sure if it’s any feasible.

Solution 1: @OnetoOne unidirectional relationship with User keeping the FK reference to the latest T&C record the user has agreed to. The T&C record will keep user_id as non primary and non unique FK field.

@Entity
@Table(name = “user”)
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name =“user”)
  Long id;

  @Column(name = “username”)
  private String username;

     .........

  @JoinColumn(name = “terms_record_id”)
  @OneToOne(cascade = CascadeType.ALL)
  private TermsRecord termsRecord;
  // joined on terms_record_id to get only the latest version accepted
}

And the entity to record version user has accepted

@Entity
@Table(name = “terms_record”)
public class TermsRecord {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = “terms_record_id”)
  private Long id;

  @Column(name = “agreed_on”)
  private Date agreedOn;

  @column(name =“version_no”)
  private String versionNo;

  // I’m not sure if it needs to have userId in here as well?
  @column(name = “user_id”)
  private Long userId;

   ........
}

And in the DB

create table user {
   user_id bigint as Primary Key,
   ...
   terms_record_id FOREIGN KEY references terms_record(terms_record_id);
   // to refer to the most updated version
}

create table terms_record {
   terms_record_id bigint NOT NULL,
   user_id bigint,
   PRIMARY KEY(terms_record_id),
   FOREIGN KEY(user_id)  REFERENCES User(user_id)            cascade on delete
   // to keep the records of terms user has agreed to
}

In the code, it will be something like:

User userA = getUser(user_id);
TermsRecord tr = new TermsRecord();
tr.setVersionNo(“1.0.5”);
tr.setAcceptedOn(....);
userA.setTermsRecord(tr);
save(userA);

So in this way, if user A had accept to terms version 1.0.3 before, it won’t delete the entry from terms_record table but it will pull a different versionNo when you retrieve userA from database.
Eg.

userA.getTermsRecord.getVersionNo == 1.0.5

And In terms_record table

  terms_record_id |     version_no
______________________________________
..........        |        .........
    3             |         1.0.3
    5             |         1.0.5
 ........         |        ..........

Solution 2

Unidirectional @OneToMany annotation in user class
With foreign key user_id stored in TermsRecord, and cascade on delete. Then retrieve the latest version manually.


texts: Delete rows within a given time interval

texts:
I have created a table with the following values:

create table MyTable (id int, dtdate datetime, serialNum int, serialCode int, status int);
insert into MyTable values
(1, 2012-12-20 01:01:00, 1, 10, 3),
(2, 2012-12-20 01:01:00, 1, 10, 4),
(3, 2012-12-20 01:05:00, 2, 11, 3),
(4, 2012-12-20 01:08:00, 2, 13, 4),
(5, 2012-12-20 01:13:00, 1, 10, 6),
(6, 2012-12-20 01:38:00, 1, 10, 3),
(7, 2012-12-20 01:39:00, 2, 11, 3),
(8, 2012-12-20 01:44:00, 1, 10, 3),
(9, 2012-12-20 01:52:00, 1, 10, 3),
(10, 2012-12-20 01:58:00, 2, 20, 4);

I would like to delete rows from the table that have the same serialNum and serialCode within an interval of 30 minutes and the same to be repeated again with the rows that falls within the next 30 minutes. So in the end, I would have the following result:

(1, 2012-12-20 01:01:00, 1, 10, 3),
(3, 2012-12-20 01:05:00, 2, 11, 3),
(6, 2012-12-20 01:38:00, 1, 10, 3),
(7, 2012-12-20 01:39:00, 2, 11, 3),
(10, 2012-12-20 01:58:00, 2, 20, 4);

I have the code below that only deletes the consecutive duplicate rows. I am not sure how I can incorporate datetime with where clause.

delete from MyTable_duplicate where nSequence in (
select nSequence from MyTable m1
where 0 < (
  select count(*)
  from MyTable m2
  where m2.id = m1.id - 1
  and m2.serialNum = m1.serialNum
  and m2.serialCode = m1.serialCode)
  GROUP BY UNIX_TIMESTAMP(dtdate) DIV 1800 order by id);

Here, MyTable_duplicate is a copy of MyTable. Any help/idea would be highly appreciated!
 Thanks


texts: Degrading PostgreSQL 8.4 write performance

texts:
Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions per second to almost zero. Write transactions are in given case insert/update/delete database transactions. The load driver used for testing the database executed SQL queries in parallel threads and used prepared statement and connection pooling. Postgres performance degraded in a couple of minutes after the first run of the test, and the problem was reproducible with only 2 parallel client threads. Subsequent test executions showed degraded throughput since the beginning. The degradation has been detected only in case of write transactions - select transactions were not affected. After some time or after server restart the problem is reproducible - test achieves high throughput and then degrades again. Linux top does not show any postgres processes performing any significant work, CPU usage during the test after degradation is <1%, io waits are also normal.
Machine used for the test is:

Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
8 CPU @ 2GHz
16GB RAM
WAL and data are on separate SSD drives

Server is initially configured as dedicated OLTP transaction processing:
Options changed from default:

max_connections = 150
shared_buffers = 4GB
wal_buffers = 16MB
checkpoint_segments = 80
maintenance_work_mem = 2GB

Modified kernel params:

kernel.shmmax = 8932986880
kernel.shmall = 2180905
kernel.sem = 500 64000 200 256

Disabling and tuning autovacuum did not give any results.
Any ideas how to fight this problem?


texts: How do I alter index rebuild an entire database?

texts:
I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'.  I try the following command:

ALTER INDEX all ON db REBUILD

And I get the following error:

Cannot find the object "db" because it does not exist or you do not have permissions

What am I doing wrong?


texts: Why is my view selecting hundreds of duplicates?

texts:
This view selects 696 entries. The CSV file has 48 entries.
CREATE OR REPLACE VIEW insert_3_char_abts AS SELECT
    ext.construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    ext.g_batch,
    ext.p_batch,
    emptycol,

    c_batch,
    abts5_mean,
    abts5_SD,
    abts5_n,
    abts5_method,
    abts5_study_id,
    abts7_mean,
    abts7_SD,
    abts7_n,
    abts7_method,
    abts7_study_id,
    pur.pk_purified_enz_id
FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      emptycol VARCHAR2(50),

      c_batch VARCHAR2 (50),
      abts5_mean NUMBER (5, 2),
      abts5_SD NUMBER (5, 2),
      abts5_n NUMBER (3),
      abts5_method VARCHAR2 (50),
      abts5_study_id VARCHAR2 (8),
      abts7_mean NUMBER (5, 2),
      abts7_SD NUMBER (5, 2),
      abts7_n NUMBER (3),
      abts7_method VARCHAR2 (50),
      abts7_study_id VARCHAR2 (8))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_view_before_insert_char_abts.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CHAR_ABTS.CSV')
    REJECT LIMIT UNLIMITED) ext
INNER JOIN purified_enz pur
ON ext.p_batch = pur.p_batch
INNER JOIN produced pr
ON pr.pk_produced_id = pur.fk_produced_id;
;

If I finish this statement with
AND pr.fk_construct_id = ext.construct_id;

It selects 46 out of 48 records, which is better, but not great.


texts:
I work on a ship that has multiple SQL Servers, both 2005/2008 versions.

As the ship moves from area to area so does our timezone. Sometimes we can jump ahead one hour and then back again.

My question is what is the best practice for this sort of situation, is it safe to change the system time so repeatedly? Should the DB be stopped before doing so? What sort of problems can arise?

Any feedback appreciated.

Thanks,
Kris


texts: Windows could not start the SQL Server (mssqlserver) service on local computer error 17058

texts:
I'm working with virtual machine on the windows 8. There is a SQL Server 2008 on the virtual machine. And in the services; when I try to start the SQL Server service getting an error like this:

Can anyone help me?

Thanks.

Edit:

The OS of virtual machine is windows server 2008.


texts:
I am loading a Qlikview CalData PGO XML file into a SQL table.
Dates in this XML are stored as a 64 bit HEX string and I need to convert it to a date.
How do I do that?
Example
40e58f2c4153d0f9 should be converted to 18/11/2020 09:11:29
I have seen some sample code within qlikview, which is
daystart(date($(HEX64CONVERT(ToBeDeleted))))


texts: How to grant connect and read/write permission to ALL databases?

texts:
I want a SQL Server login that can READ, WRITE and View definition on all databases.

After doing:

USE master;
GRANT VIEW ANY Definition TO [a];

It looks like I have to GRANT CONNECT and read/write to each and every single database.

Isn't there an easier way?


texts: What happens in Microsoft SQL Server 2012 .ldf when the database log file if full?

texts:
Recently we found out that .ldf file of our  Microsoft SQL Server 2012 database was growing large. We have set limit as follows:

Now we have a question: What will happen when log .ldf file reaches its size limit?

Thank you


texts: Upgrade Oracle RAC to version 19c - runcluvfy not passed because of missing patches - but they are applied!

texts:
Good morning all,
I hope anyone can help me with this. I am trying to upgrade an Oracle RAC of two nodes from version 12.2 to 19c. As part of the prerequirements, I have applied the patch 28553832 in both nodes. Opatch and Kfod show both the same patches applied to both nodes
Node 1
$crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [12.2.0.1.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [1205374555].
$crsctl query crs softwarepatch
Oracle Clusterware patch level on node scglvdoraci0010 is [1205374555].
$crsctl query crs releasepatch
Oracle Clusterware release patch level is [1205374555] and the complete list of patches [26839277 27105253 27128906 27144050 28553832 ] have been applied on the local node.
$ kfod op=patches
---------------
List of Patches
===============
26839277
27105253
27128906
27144050
28553832
$ kfod op=patchlvl
-------------------
Current Patch level
===================
1205374555

Node 2
$crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [12.2.0.1.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [1205374555].
$crsctl query crs softwarepatch
Oracle Clusterware patch level on node scglvdoraci0010 is [1205374555].
$crsctl query crs releasepatch
Oracle Clusterware release patch level is [1205374555] and the complete list of patches [26839277 27105253 27128906 27144050 28553832 ] have been applied on the local node.
$ kfod op=patches
---------------
List of Patches
===============
26839277
27105253
27128906
27144050
28553832
$ kfod op=patchlvl
-------------------
Current Patch level
===================
1205374555

However, when I run the runcluvfy I got this error
/grid122/app/19.3.0/grid/runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /grid122/app/12.2.0/grid -dest_crshome /grid122/app/19.3.0/grid -dest_version 19.0.0.0.0 -fixup -verbose

Verifying Oracle patch:28553832 ...
  Node Name     Applied                   Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  scglvdoraci0009  missing                   28553832                  failed
  scglvdoraci0010  missing                   28553832                  failed
Verifying Oracle patch:28553832 ...FAILED (PRVG-1260, PRVG-1261)

My question is how cluvfy is checking which patches are applied and why it is getting this error , when kfod and lsinventory in opatch show both that the patch is applied in both nodes.
Would anyone in this scenario try to upgrade when the requirements are not passed ?
Thank you


texts: Create View using table and column names from data in a table, then build SQL

texts:
I need to create a view using data in a table for the table name and column name.  Preferably I’d like to do it with set processing. I have a sample of doing this for one record in the table with a script of stored procedure, which won’t do for the application.  The resulting view pulls data from a data logger for display on a dashboard, so it needs to be current and fast.  That’s why I think a view would work best, but I’m having trouble coming up with the SQL.

I have a table that looks like the following.  This is an example of two records, there are tens of thousands of them.  There will be one or more duplicate table names, which corresponds with one or more column names in the LOGGER_FIELD column.

From data in this table, I’d like to create a view that would have this sql:

select TmStamp, site_id, value
from
     (select TmStamp, G0025890, G0025891
      from   [BIG SLOUGH (STN 953)_HOURLY]
      where TmStamp = (select max(TmStamp) from [BIG SLOUGH (STN 953)_HOURLY])) p
unpivot
(value FOR site_id IN (G0025890, G0025891))AS unpvt;

The table [BIG SLOUGH (STN 953)_HOURLY] looks like:

The table name in the FROM is tabname in the view, and the values in LOGGER_FIELD are the column names.  The ending view will look like:


texts: Storing floating point values for various units of measurement (kg, ml, mm, pl)

texts:
I have a table like the following:

order_item
------------
serial      | id (PK)
integer     | order_id
????        | quantity
integer     | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc.

I'm doing an application where I need to store the amount of material used in chemical analysis. Sometimes materials are used in such a small amount as microliters, or sometimes more, like one or two liters.

Is it the numeric data type good enough?

I have read that it is a good idea to convert the values to the smallest unit of measure. So if I have 10 liters, I have to pass to 10000000 microliters. But what if you need to use a unit of measurement even lower in the future? like picoliters? Inevitably, the application will have to use floating point numbers I think.


texts: Postgresql Create ForeignKey with repeated table name in value of ref contrain key

texts:
I am working on a database which does not contain any foreign key. When i open table I see strange format key because table of parent reference is stored in the value.
exemple
CREATE TABLE foo
  (
  id SERIAL PRIMARY KEY,
  name text
  );

CREATE TABLE bar
  (
  id SERIAL PRIMARY KEY,
  foo_id text,
  name text
  );

INSERT INTO foo("name")
VALUES ('john');

INSERT INTO bar("foo_id","name")
VALUES (CONCAT('foo.', 1),'doe');
INSERT INTO bar("foo_id","name")
VALUES (CONCAT('foo.', 1),'kelly');

Can I create an FK with this type of structure? I have never seen this before


texts:
I have got tables:

children_tab
id
name
lastname

parent_tab
id
name
lastname

children_parent_tab
children_id
parent_id

There is also parent view that return in field children_ids elements from children_parent_tab

I have to search parents by children ids

Let's say that parent contain:

1|name1|lastname1|{1}
2|name2|lastname2|{5}
3|name3|lastname3|{3,7}

SELECT * FROM parent p WHERE ARRAY[1,2,3,4,5]::int[]  @> children_ids

will return only parent with id 1 and 2 but without that one with id 3. How that query can be changed to return also parent with id 3 because I'm looking for parent of child with id 3?


texts: I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one instance is connected. I checked sp_who2 results which shows that there are many AWAITING COMMANDS and it takes up most of the CPU time. Please advise if more number of awaiting commands can cause high CPU utilization.


texts: Execute new query when current query finalises

texts:
I am in the process of inserting some data into a SQL Server (2016). Since this is a manual one-time load, I have not bothered spending a lot of time writing it the most efficient way; however some of the loads takes much longer than initially anticipated - so here is my question.

Can I somehow tell the server to execute a nother query immediately after the current one finalises? I would have done this manually by hitting F5 on the next part of the code - but since the load takes longer than expected, I would like the server to somehow execute the next part of the code automatically, when the first part finalises.

Is this at all possible without either 1) waiting for the current query to finalise or 2) cancelling the current query and re-running the whole lot at once including my next steps of my code?

Hope this makes sense!

Thanks :-)


texts: How to restore hundreds of GB database present on SQL Server 2014 to SQL Server 2008 R2

texts:
I know that this was not possible as a scenario for SQL Server 2012 -> 2008 but I wonder if MS made any changes here with the 2014 version.

We have SQL Server 2014 version with a fairly large database, We’d like to restore a backup of that database to SQL Server 2008 R2 because that physical machine has more space, RAM,…

I’m getting standard error message when I try to restore backup but I was wondering if there is something else in SQL Server 2014 that I might be missing.

Also, are there any tools like Redgate/SQLSentry that will be of any use while working with databases size of hundreds of GB size?


texts: Reason for using hexadecimal in NCHAR()?

texts:
I found this in some source code today:

        SELECT
            @Error = ERROR_NUMBER (),
            @ErrorMsg = N'An Error occured while populating the TABLE ' + @DestinationTableName +
                        N' with data. Error Code: ' + CAST ( @Error AS nvarchar(20) ) +
                        N', Error Description: "' + ERROR_MESSAGE () + N'".'
                        + NCHAR(0X0D) + NCHAR(0X0A) + NCHAR(0X0D) + NCHAR(0X0A),
            @ErrorSeverity = ERROR_SEVERITY (),
            @ErrorState = ERROR_STATE () ;

It looks like the error message string is adding two line feeds after the description. 0X0D is 13 and 0X0A is 10.

Is there a reason to use hexadecimal instead of just integers?

Normally what I've done is NCHAR(13) + NCHAR(10)...


texts:
In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats?  I don't see any obvious common ID columns and INFORMATION_SCHEMA.COLUMNS doesn't help either.  I'm trying to beef up a simple querystore text search query to include usage and performance detail from the runtime_stats view.
Here's what I'm using currently, which works fine to identify queries containing a search string:
DECLARE  @SearchString NVARCHAR(100)=''
SELECT q.query_id, q.query_text_id, t.query_sql_text, object_name(q.object_id) AS parent_object, q.last_execution_time
FROM sys.query_store_query_text t JOIN sys.query_store_query q
   ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE '%' + @SearchString + '%'

I want to add a handful of the stats from sys.query_store_runtime_stats to this, if it's doable.


texts: Error ORA-12505 when trying to access a newly installed instance of oracle-11g express

texts:
I encounter problems installing a local instance of oracle express on my Windows 7 - SP1 computer.
I did a basic installation, as administrator (run as 'administrator' on setup.exe).

Then I tried to connect with Oracle Sql Developer with the flollowing parameters :

Username : system
password : the one I provided during installation
hostname : localhost
role :sysdba
port : 1521 (didn't change the default one)
SID : XE.

And I always get :

  Statut : échec -Echec du test : Listener refused the connection with the following error:
  ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

My window user is in ORA_DB group, as well as the administrator user.

When I try to connect as sysdba in SQL command prompt, I got :

ORA-12638: Credential retrieval failed

I also tried to use servicename instead ('orcl'), but it failed with ORA-12514 instead.

My listener.ora looks like this:

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = XE)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        (ADDRESS = (PROTOCOL = TCP)(HOST = MACHINE0138.com.company.intra)(PORT = 1521))
      )
    )

  DEFAULT_SERVICE_LISTENER = (XE)

The result of "lsnrctl status"

  LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 08-F╔VR.-2016 10:40:01

  Copyright (c) 1991, 2014, Oracle.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER

  Alias                     LISTENER

  Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production

  Start Date                08-F╔VR.-2016 10:37:59

  Uptime                    0 days 0 hr. 2 min. 6 sec

  Trace Level               off

  Security                  ON: Local OS Authentication

  SNMP                      OFF

  Default Service           XE

  Listener Parameter File   D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server\network\admin\listener.ora

  Listener Log File         D:\Programmes\BDD\OracleExpress\app\oracle\diag\tnslsnr\MACHINE0138\listener\alert\log.xml

  Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE0138.com.company.intra)(PORT=1521)))

  Services Summary...

  Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

  Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

  The command completed successfully

And eventually, here's the log.xml file after my attempt :

<msg time='2016-02-08T11:17:55.181+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>08-FÉVR.-2016 11:17:55 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=GLE10445))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58750)) * establish * XE * 12505
 </txt>
</msg>
<msg time='2016-02-08T11:17:55.185+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
 </txt>
</msg>

Any tip welcolmed, indeed.
And thanks in advance for reading until this here!


texts: Clustered Index with nightly ETL process

texts:
We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting.  The clustered index on the table consists of a composite key of four columns.

Due to where the data is coming from, there really isn't any way to load the data in the same order as the clustered index.  Keeping the index active during load or dropping/rebuilding it after the load both take more time than the load itself without an index.  On the other hand no clustered with proper non-clustered indexes can still result in some reporting queries taking hours to process.

The only things I can think of to speed this up would be partitioning and throwing more hardware at the problem.  Am I missing anything else that would help?


texts: Complete Failover Cluster Failure SQL Server 2012

texts:
We have experienced a full cluster failure on our 3 node cluster running SQL Server 2008 R2 with 7 SQL Server instances. It started when we rebooted one of the nodes and it returned an error:

  Node '%1' failed to establish a communication session while joining the cluster. The was due to an authorization failure. Please verify that the nodes are running compatible versions of the cluster service software.

The other two nodes were still online. Running a cluster validation test passed. After consulting with Microsoft we decided to take down all 3 nodes, bring one node online with force quorum option (so the cluster would come up on a single node), then bring the other nodes online one at a time.  Unfortunately, after doing this, we were left with a single node forced online, because neither of the other nodes would join. The same error above was being reported on both the other nodes.

No resolution has been found for this after 2 weeks of investigation so we were presented with two options:

Rebuild the cluster
Evict the nodes and try to get them rejoined

My question is, this cluster has 7 SQL Server instances on it, and one File Server service. If the cluster is completely rebuilt, or a node is evicted, what are the steps to get SQL Server back working? Do we have to completely reinstall SQL Server or dDo we just have to add the instances back into the new cluster using Failover Cluster Manager without lengthy software installs?


texts: Visualizing SQL Server Extended Events data

texts:
Lately I've been exploring using Extended Events in SQL Server to help me benchmark and optimize various queries. So far, to view the event data I have been using the "Watch Live Data" feature in SSMS.

The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask:

Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)
Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?

UPDATE

As requested, here is the session:

CREATE EVENT SESSION [Simple Query Benchmarking] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(1000))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO


texts: Problem with Script to Synchronize Login on AlwaysON

texts:
I'm trying to resolve an issue. I've a two nodes with Always On enabled, I want to synchronize user over the nodes. I see that fantastic script.

https://maq.guru/synchronizing-sql-server-logins-in-an-always-on-availability-group/

Only one problem, when an user SQL have Policy enabled tghi script can't change password on other node.

Error message:

  Invoke-Sqlcmd : A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
  A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
  At C:\Users\Administrator\Desktop\AutoCloneLogins.Or.ps1:343 char:17
  + ...             Invoke-Sqlcmd -Query $Query -ServerInstance $SecondaryRep ...
  +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
      + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand**

Please help me to resolve this issue.


texts: Get the Largest Count of Consecutive Value

texts:
I have this table that records win and lose. Let us say this is my table

create table test(
  col1 int not null
)Engine=InnoDB;

it has 10 rows :

0,1,1,1,0,1,1,0,0,1

1=Win and 0=Lose. I want to get max count of the consecutive win and lose. For this example, the max count for consecutive win is 3 and for lose is 2. How can I get my desired result? I'm not sure where to start with this one. Thanks in advance.

EDIT

create table t1 (
 colid int not null auto_increment,
 col1 int,

 primary key(colid)
);

insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (0);
insert into t1(col1) values (1);

select
a.col1,
max(a.rn)
from (

        select
        x.col1,
        if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn, @prev := col1
      from t1 x , (select @prev := - 1 , @current := 1) v
      order by x.colid
      ) a
group by a.col1

this one works the way I see the results.. Is there anything I should consider? I only have knowledge on basic joins. I haven't fully understand the query as of now..


texts: Is there a way to merge two databases, same schema, with a lot of foreign key costraints and incremental id into one single database?

texts:
DATABASE A
users
id name profile_id
1  jhon          1
2  smith         2

profile_id:
id email user_id
 1 a@g.c       1
 2 c@g.c       2

DATABASE B
users:
id name  profile_id
 1 elvis          1
 2 black          2

profile_id:
id email user_id
 1 b@g.c       1
 2 d@g.c       2

I want to merge
users:
id name profile_id
 1 jhon          1
 2 smith         2
 3 elvis         3
 4 black         4

profile_id:
id email user_id
 1 a@g.c       1
 2 c@g.c       2
 3 b@g.c       3
 4 d@g.c       4


texts:
Please let me explain my problem and situation:

I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics. I also created a maintenance plan to rebuild indexes daily. After those additions, the application has been behaving unstable; it would work fast for couple of hours then it would start timing out again. Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.

I've been trying to figure out how to fix those queries and when I profile it and paste the query directly into the SQL Management Studio it returns the results in 1 second, but when I run this query from my application, it's about 25 seconds. Then after it runs for the first time, next time it goes as fast as on the server!

I started doing some research and it looks like when I played with all those indexes my query plans got messed up and now they are creating issues.

My questions are :

Should I refresh my query plan cache (around 22000 queries - a lot of them has been used only once) and
If I do it, what would the impact be on the SQL while they are all rebuilding?


texts: MySQL replication of single DB to same server

texts:
I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).

I have dozens of databases but one of them is being hit quite hard with large updates which degrade the performance of other queries.

To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.

I have found a lot of guides on the internet describing how to replicate to a different server, and one (http://www.howtoforge.com/one_machine_mysql_replication) describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already.  It also talks about compiling MySQL from source.

Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?

My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.

Thanks in anticipation.


texts:
There were very good articles in MSDN and all over, but none explained about the BPE size or max size setting or growth.

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);

This is going to add the file of 2GB, but what I understand was this is the minimum size of buffer pool file which it will be starting with, and this will be the max memory size setting on sql server?

and is there a way i can  set how large the BPE file can grown... even  though i plan the SSD drive to be 1:4 ratio ( 1 being maxmemory setting in sql).. Could some one please clarify.


texts: Replicate Postgres 9.2 to 9.4 with no downtime

texts:
My current set up has a database running on Postgres 9.2.4 and would like to move from AWS EC2 to RDS.  However, AWS requires Postgres be running at least Postgres 9.4 in order to perform this migration.

With the goal of minimal downtime, as this is a production-level database, I'm looking for a solution that would allow me to replicate between versions of Postgres, specifically 9.2 to 9.4.  Performing a pg_upgrade requires bringing the database down, and took around 25-30 minutes to complete. We're looking to have our downtime be significantly less than half an hour, if possible.

Has anyone successfully migrated from 9.2 to 9.4 with little to no downtime? Or other suggestions that may minimize how long the database must be down for?


texts: How to query data of changes in a column in MySQL


texts:
Initially we installed all extensions in default "public" schema.
Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common.

Unfortunately AWS RDS reports back when trying to move the existing ones like this:

ALTER EXTENSION "hstore" set schema common;

DBCException: SQL Error [42501]: ERROR: must be owner of extension hstore
  org.postgresql.util.PSQLException: PSQLException: ERROR: must be owner of extension hstore

I am using the default user that is also member of role rds_superuser.


texts: MySQL query took extremely long time to finish and it was in 'Sending data' state

texts:
This query took extremly long time and it never seemed to finish.

Below is the query:

INSERT INTO `zhihu_answer_meta_reconcile`

    SELECT a.*
    FROM `zhihu_answer_meta` a
    LEFT JOIN `zhihu_answer_meta_staging` b
            ON a.answer_id   = b.answer_id
           AND a.insert_time = b.insert_time
    WHERE b.answer_id IS NULL

UNION ALL

    SELECT *
    FROM `zhihu_answer_meta_staging`

The row numbers of these tables are:

zhihu_answer_meta_staging: 1074588
zhihu_answer_meta: 2315697
zhihu_answer_meta_reconcile: 0  this table is a newly created table for inserting data into.

Besides, both of three tables' structure is the same.

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Appretiate if anyone can give some advice. Thanks a lot.


texts:
How can you tell when a database is over normalized? Example: I have a database for employee. These are the following tables involved:

User - contains email, password, companyId
Profile - profile of the employee (first name, last name, address)
Group - Department list
Company - list of companies
Position - List of positions
Employee - employee information like id numbers, height, weight, contract info, etc.


texts: What are the considerations when using SERLIALIZABLE over READ COMMITTED isolation level in Oracle?

texts:
What are the considerations when using SERIALIZABLE isolation level over the default READ COMMITTED in an Oracle database?

Given the concurrency system used within Oracle (using transaction logs to determine the state of the database at a point in time),  I understand that using a SERIALIZABLE transaction does not decrease the availability of the database over READ COMMITTED isolation.

One consideration is that there must be enough space allocated for undo segments to allow the database to store enough history to accommodate the implied extra time required to support transaction-level over statement-level read consistency. If there is not enough undo data, there is a risk of error ORA-01555 Snapshot Too Old (although this is still an risk with a READ COMMITTED query).

One point that I am not clear on is if there are any performance implications of serializable isolation. Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Finally, as I am talking about a read-only transaction the assumption is that there is no risk of a ORA-08177: Cannot serialize access for this transaction from writing to data which has since been updated.


texts: I deleted a folder from /var/lib/mysql and now all of my databases seem to be unaccessible

texts:
I was receiving this error when trying to drop a database:
ERROR 1010 (HY000): Error dropping database (can't rmdir './redpopdigital@002ecom', errno: 39 "Directory not empty")

So I went into /var/lib/mysql and just did an rm -rf. I did not know that this would screw with literally every other database.
Now it seems all of my databases are inaccessible.
I tried this as a troubleshooting step:
ubuntu@blainelafreniere:~$ mysqlcheck --repair blainelafreniere -u root -p
Enter password:
blainelafreniere.wp_commentmeta
Error    : Table 'blainelafreniere.wp_commentmeta' doesn't exist in engine
status   : Operation failed
blainelafreniere.wp_comments
Error    : Table 'blainelafreniere.wp_comments' doesn't exist in engine
status   : Operation failed
blainelafreniere.wp_links
Error    : Table 'blainelafreniere.wp_links' doesn't exist in engine
status   : Operation failed

what's weird is, all of the data appears to be chilling in /var/lib/mysql... but I can't access it for some reason?
Is there any hope of recovering the data from /var/lib/mysql or am I completely screwed?
Thanks.


texts: How to clear an Error 15404 after a DB restart (besides rebooting)

texts:
Every so often (e.g. ~months) a SQL Server Agent hourly Job will begin reporting an Error 15404 and continue to do so until intervened with.

  [298] SQLServer Error: 15404, Could not obtain information about
  Windows NT group/user 'DOMAIN_NAME\SomeDomainAccount', error code
  0x6e. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Sometimes the first failure occurs immediately after a manual restart of the SQL Server Engine and SQL Server Agent services.  The problem can be cleared by rebooting the machine.

The Job Owner is the name listed in the error message and is a SQL Server Admin.

The SQL Server Engine Service account looks to be a service account (I believe it is the default install account (one notch better than generic NetworkService to prevent interference between Engine/Agent instances):

   NT Service\MSSQL$INSTNAME

It would be one thing if the job always failed but since the job succeeds after a reboot it makes me think that a service account like is supposed to be working and that there is some A/D timing issue or possibly a bug.  When IT is asked about the A/D configuration, the response is usually "nothing has changed."

Restarting the engine and agent services can cause the job to start failing.
A machine reboot clears the problem.
An immediate subsequent restart of the engine and agent no longer cause the job to fail.

Link:
How to troubleshoot a SQL Server 8198 error


texts: MySQL: Replicate data within 2 same schemas on 1 mysql server

texts:
This might sound a bit crazy, please let me explain the reasoning behind this:

we plan to use a real master-slave replication for production
we want to use something small'n easy for local development

We mostly have Java/PHP devs who should be able to inspect state changes of master/slave databases. The easiest (infrastructure-wise) way seems to be just have a one MySQL server with 2 same schemas (so master/slave is not server/server, but schema/schema instead).

Is this easily achievable with MySQL or do we have to install 2 MySQL servers even for local testing?


texts: How to write this JSON-preparing, non-scalar query as scalar?

texts:
I use something like the following to prepare a block of json:

SET @json = (SELECT
  @varchar1 as MyVarchar1,
  @varchar2 as MyVarchar2,
  GetUtcDate() as MyTime FOR JSON PATH);

Visual Studio complains that it's a "Non scalar subquery in place of scalar" - is there a way to write it so it doesn't elicit a warning?


texts: MSSQL 2016 - Restore SSISDB from MSSQL 2012

texts:
On "MSSQL 2016 (SP2-CU11) (KB4527378)" I created empty SSISDB. After that I restored SSISDB from "MSSQL 2012 (SP4) (KB4018073)" to "MSSQL 2016 (SP2-CU11) (KB4527378)" On DB I set owner to sa and version to Compatible Level 2016(130).

On Integration Services I want to do upgrade. I click on Database Upgrade and I get error:

  The system cannot find the file specified (System)

Program Location:

   at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
   at System.Diagnostics.Process.Start()
   at Microsoft.SqlServer.IntegrationServices.UITasks.ActionHandler.PerformAction(String actionMoniker, IContext context)

How can I solve this problem?

Some screenshots

I tried to run wizard from disk a I got error:


texts: How does one map nested multi valued attributes?

texts:
Let's say one has an Entity E.

E has a multivalue Attribute M1 and a simple attribute A1.
However M1 consists again of a multivalue Attribute M2 and a simple Attribute A2.

How would one map this diagram in case M1 is a PK and is not a PK?

Any help is welcome. I included the basic diagram below:

I expect something like:

First Case:

M1(Id[E], A1, Surrogate)
M2(Value, Id[M1], A2) with A2, Id[m1], value being primary key

Second case:

E(Surrogate[M1], A1)
temp(Surrogate)
M2(Surrogat[temp], A2, Value)

I'm more interested in the logical design that the SQL / physical implementation.


texts: Invalid object name 'sys.dm_os_volume_stats'

texts:
I am trying to select 15% of disk space available in a server.

 SELECT DISTINCT
            s.volume_mount_point [Drive],
            CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs],
           ((CAST(s.total_bytes / 1048576.0 as decimal(20,2)))*15)/100 AS [FifteenpercentAvailableMBs]

            FROM
                    sys.master_files f
                    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s

This query works well in Microsoft SQL Server 2008 R2 (SP2)

But it wont work in Microsoft SQL Server 2008 R2 (RTM)

How can I make this query work in 2008 R2 RTM? (I mean is there any alternative query that I can use to get 15% of available space?)


texts: postgres btree index on large text column

texts:
I have a table with big text column on which I want to enforce uniqueness,
postgres can only do this with a btree index. I get the following error :

CREATE unique INDEX idx_protein_seq_sequence ON protein_seq USING hash (sequence);

[54000] ERROR: index row requires 8736 bytes, maximum size is 8191

Are there solutions ?

I'm thinking of using a trigger (update and insert) and find duplicates with a hash index (a hash index seems to have no limit on size of text columns);


texts: Is there a way of securing ProxySQL's own SQLite3 database?

texts:
As I understand ProxySQL has support for SSL encryption for inbound and outbound traffic (front/backend) and supports password hashing. However back in 2017, one had direct database access:
How to easily bring 80 MySQL users into ProxySQL?
According to Icarus's answer, it would be rather easy for someone who broke into the hosting server to gain access to all endpoints listed on this database by making a select call directly to it.
Is there any way of encrypting the SQLite3 DB? Or perhaps of encrypting the data stored in the database (similar to how SSL is used for in-transit data)? Basically looking for data-at-rest securing strategies.
Perhaps this is already implemented in recent versions? I could not find any documentation supporting it, but it did seem that may be the case:
https://github.com/sysown/proxysql/blob/v2.0.13/lib/ProxySQL_Admin.cpp
Which, if I understood correctly implements SHA1 encryption:
https://stackoverflow.com/questions/3179021/sha1-hashing-in-sqlite-how
If this is correct, is this restricted to password hashing? Or is it part of the SSL encryption implementation? Or is there actually a way of enabling encrypted "DISK" read/writes? Is this by any chance implemented by default on v2+ builds?
Ultimately, I plan on encrypting the volume this is installed in, and have a second proxy layer so that I don't expose endpoint information; this would be an additional layer of protection.
Thanks, I appreciate any insights you may have!


texts:
I am trying to delete all the duplicates but keeping single record only (shorter id).
Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones.
DELETE FROM emailTable WHERE id IN (
 SELECT * FROM (
    SELECT id FROM emailTable GROUP BY email HAVING ( COUNT(email) > 1 )
 ) AS q
)

Its MySQL.
DDL
CREATE TABLE `emailTable` (
 `id` mediumint(9) NOT NULL auto_increment,
 `email` varchar(200) NOT NULL default '',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=298872 DEFAULT CHARSET=latin1


texts: Database design for varying amount of relations

texts:
I am currently trying to design a database model to store all the possible nutrition data, but for many products there isn't as much data available as for the others, as a result many field could end up as NULL values (I do not want to use any placeholder/default values as I will need to be able to separate which ones are actually missing)

I found that PostgreSql might be a good solution for me, as I do not want to build a EAV (Entity-Attribute-Value) tables, because I have had my experience with it, it doesn't scale very well. (If "products" could have anywhere from 0 to 200 attributes, it would quickly get into tens of millions rows).

PostgreSql has composite types and you can also store them as an array, which allows me to store key value pairs easily within a single column, as a result it is easy to lookup everything related to the "product", I can easily do a join between tables and have the data available.

Now the question is:

Is it a good solution, what kind of obstacles could I face, is there a better solution to my problem?

Would having separate tables for example:

CREATE TABLE products (
    proteins_id FOREIGN KEY,
    lipids_id FOREIGN KEY,
    minerals_id FOREIGN KEY,
    vitamins_id FOREIGN KEY)

each foreign key referencing a table with possibly up to 50 null values in a row

OR

CREATE TYPE custom_type (
   field_1 INTEGER,
   field_2 INTEGER
)

CREATE TABLE products (
    proteins custom_type[],
    lipids custom_type[],
    minerals custom_type[],
    vitamins custom_type[]
)

I understand that the optimal solution might be somewhere in-between those two solution by finding a balance of some sort, but I really need some pointers on that, thank you guys! :)

EDIT: Plus another things to consider: The tables might need to be expanded quite often and it would be great if the process were least painful.

I also face the same kind of problem with other tables such as (food) "recipes" as the number of ingredients can be very varying plus I need to store the measures/quantity of the components.


texts: Postgres - Revoke access to public schema for an user

texts:
As a DBA, I have created an user 'user1' to access 'abcd' schema as per the request. But, the user I have created is also getting read access to the schema 'public' in the database. An application owner requested me to REVOKE the access to 'public' schema from the user 'user1' that I have created. For that, I am using the following REVOKE statements which are not revoking the access to 'public' schema for the user 'user1'. Am I missing something here? I have also taken the pg_dump of the database with 'schema-only' to see if there any other grants that are assigned to the user 'user1' but found none.

REVOKE ALL ON schema public FROM user1;

and then tried;

REVOKE USAGE ON SCHEMA public FROM PUBLIC;

I am still able to read a table in the 'public' schema from 'user1' user.


texts:
In our environment we have a 2-node Failover cluster instance and log shipping for disaster recovery. Failover cluster is the primary site and log shipping is on DR. 2 node: same data center; log ship is on another data center.

What is the best practice when a disaster strikes, failover with data loss or try to bring the primary up or else failover?

There is no RPO/RTO defined. I am the only one who manages. With log shipping my maximum data loss is 5 minutes. It will take 30 minutes to failover.


texts: SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size

texts:
I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX).

The problem I'm having is after executing all the alter table commands the database ends up being almost 26GB in size. I understand that from here using NVARCHAR(MAX) will alow the DB to grow more slowly but is there any way for me to prevent this bloating ?


texts:
I run MariaDB 10.0.23, and I use roles to grant privileges to users. I have a user john:

+-------------------------------------------------------------------+
| Grants for john@%                                                 |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%'                                        |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
+-------------------------------------------------------------------+

The client role is defined like this:

+--------------------------------------------------------+
| Grants for client                                      |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client'                         |
| GRANT INSERT ON `database_name`.`table1` TO 'client'   |
| GRANT SELECT ON `database_name`.`table2` TO 'client'   |
| GRANT SELECT ON `database_name`.`table3` TO 'client'   |
| GRANT INSERT ON `database_name`.`table4` TO 'client'   |
+--------------------------------------------------------+

When I connect using mysql -u john -p database_name, I get

ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'

which kind of makes sense because when users log in, they don't have a role assigned by default.
However, I get the same error when I do mysql -u john -p and then

SET ROLE client;

SHOW GRANTS;
+-------------------------------------------------------------------+
| Grants for john@%                                                 |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%'                                        |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
| GRANT USAGE ON *.* TO 'client'                                    |
| GRANT INSERT ON `database_name`.`table1` TO 'client'              |
| GRANT SELECT ON `database_name`.`table2` TO 'client'              |
| GRANT SELECT ON `database_name`.`table3` TO 'client'              |
| GRANT INSERT ON `database_name`.`table4` TO 'client'              |
+-------------------------------------------------------------------+

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

USE database_name;
ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'

The workaround that I use is to add one of the role's permissions to each user directly. This way users get access to the database, but it doesn't seem quite logical to me that using roles users still have to have permissions granted to them explicitly. MariaDB documentation doesn't say anything about this issue.

Any thoughts on why it happens and how to fix this behavior?


texts: rails 6 activerecord-oracle_enhanced-adapter drops support for primary key trigger with Oracle 12c database

texts: I have a stable Rails 5 application with models configured with the :autogenerated sequence name parameter allowing Oracle to execute database triggers on insert. I am attempting to migrate the application to Rails 6 and a compatible activerecord-oracle_enhanced-adapter is required. Rails 6 compatible versions of the activerecord-oracle_enhanced-adapter have dropped trigger based primary key support. Should I be looking to my DBA to resolve the issue, or should it (can it) be addressed with application logic?


texts: Taking Back up on Non readable Secondary Replica Availability group

texts: Is there any way to back up a non readable secondary replica database to restore in a dev environment and then do dbcc checkdb? Will appreciate any help!


texts: Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

texts:
Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary:

CREATE TABLE test (name VARCHAR(50), number TINYINT);
INSERT INTO test VALUES
    ('foo', 1), ('foo', 1), ('foo', 2), ('foo', 3), ('foo', 3),
    ('bar', 1), ('bar', 2), ('bar', 2), ('bar', 2), ('bar', 3),
    ('baz', 1), ('baz', 2), ('bar', 2);
SELECT name, number, COUNT(1) FROM test GROUP BY name, number WITH ROLLUP;

+------+--------+----------+
| name | number | count(1) |
+------+--------+----------+
| bar  |      1 |        1 |
| bar  |      2 |        3 |
| bar  |      3 |        1 |
| bar  |   NULL |        5 |
| baz  |      1 |        1 |
| baz  |      2 |        2 |
| baz  |   NULL |        3 |
| foo  |      1 |        2 |
| foo  |      2 |        1 |
| foo  |      3 |        2 |
| foo  |   NULL |        5 |
| NULL |   NULL |       13 |
+------+--------+----------+

I'm not inerested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?


texts: How to return a null from an empty XML tag in SQL server using Xpath only?

texts:
I have an application that stores various user defined data points about a record in an XML column. I have no control over how these are stored or updated.

When I query a column it can return 1 of 3 values:

The value
Null
An empty string

$64 question I would like to return those empty strings as nulls using xpath.

I've found a lot of answers about returning the null as an empty sting but nothing this way round.

They end up that way when someone has delete the value, and instead of deleting the tag it blanks it to a <value /> tag. The nulls occur when the value has never been set. They have not included xsi:nil.

I've done it with a case statement:

    select
    so.SalesOrderId,
    Case
        when sopc.value('(Value)[1]','smalldatetime') IS null then Null
        when sopc.value('(Value)[1]','smalldatetime') ='' then Null
        Else sopc.value('(Value)[1]','smalldatetime')
    End as sopc
    From
    SalesOrders so
    Outer apply so.CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[Name="ProjCompleted"]') sopc(sopc)

but this feels inefficient and this is not the only column so it makes the code long and harder to maintain.

Edit to include sample data

SalesOrderId    CustomColumns
SO 1            "<CustomColumnsCollection>
                   <CustomColumn>
                     <Name>ProjCompleted</Name>
                     <DataType>1</DataType>
                     <Value />
                   </CustomColumn>
                 </CustomColumnsCollection>"
SO 2           "<CustomColumnsCollection>
                  <CustomColumn>
                     <Name>ProjCompleted</Name>
                     <DataType>1</DataType>
                     <Value>'2017-11-21'</Value>
                  </CustomColumn>
                </CustomColumnsCollection>"
SO 3           "<CustomColumnsCollection>
                </CustomColumnsCollection>"

**Output**
Current      Desired
''           null
2017-11-21   2017-11-21
null         null

Thanks in advance.


texts: how to use the keyword `VALUES` in an `IN` statement?

texts:
I am on sql server 2016
select @@version

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)
Sep 17 2020 22:12:45    Copyright (c) Microsoft Corporation
Enterprise Edition:  Core-based Licensing (64-bit)  on Windows Server
2012 R2 Datacenter 6.3  (Build 9600: ) (Hypervisor)

Inside a simple insert statement in sql server I have the key word values
for example:
BEGIN TRANSACTION T1
SELECT @@TRANCOUNT

INSERT INTO [APCore].[usr].[userBusinessArea]
           ([businessAreaID]
           ,[userID])
     VALUES
           (21, 368),
           (21, 227419) ,
           (21, 99906),
           (21, 140470)

SELECT [1]= @@TRANCOUNT
--COMMIT TRANSACTION T1
--ROLLBACK
SELECT [2]=@@TRANCOUNT

The problem is that before I run the insert I would like to use the same logic with the values in order to find out if any of these records is already in the table.
otherwise you might end up with this:

the way I have done is:
select top 10 * from
[APCore].[usr].[userBusinessArea]
where businessAreaid = 21
 and Userid in
(368,227419,99906,140470)

this is after the insert:

I saw some examples in here: SQL Server IN Operator
Is there a good and worth way to use values in an in statement?


texts: mysql.sock not generated when starting MySQL server using XAMPP

texts:
I have XAMPP installed, and I want to use mysql.sock file for my rails app.

Now, when I start the MySQL server (from xampp control panel), it starts, I see it's PID, I can access the mysql command line etc, but I can't find the mysql.sock file anywhere.

I'm running under Windows 7.

Any idea what might could have gone wrong?

EDIT:
Here's the error log, from mysql_error.log - this is what I see when i start MySQL from xampp control panel:

2015-07-16 13:51:27 4088 [Note] Plugin 'FEDERATED' is disabled.
2015-07-16 13:51:27 16f0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-07-16 13:51:27 4088 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-07-16 13:51:27 4088 [Note] InnoDB: The InnoDB memory heap is disabled
2015-07-16 13:51:27 4088 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2015-07-16 13:51:27 4088 [Note] InnoDB: Memory barrier is not used
2015-07-16 13:51:27 4088 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-07-16 13:51:27 4088 [Note] InnoDB: Not using CPU crc32 instructions
2015-07-16 13:51:27 4088 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2015-07-16 13:51:27 4088 [Note] InnoDB: Completed initialization of buffer pool
2015-07-16 13:51:27 4088 [Note] InnoDB: Highest supported file format is Barracuda.
2015-07-16 13:51:27 4088 [Note] InnoDB: The log sequence numbers 1665558 and 1665558 in ibdata files do not match the log sequence number 1666100 in the ib_logfiles!
2015-07-16 13:51:27 4088 [Note] InnoDB: Database was not shutdown normally!
2015-07-16 13:51:27 4088 [Note] InnoDB: Starting crash recovery.
2015-07-16 13:51:27 4088 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-07-16 13:51:27 4088 [Note] InnoDB: Restoring possible half-written data pages
2015-07-16 13:51:27 4088 [Note] InnoDB: from the doublewrite buffer...
2015-07-16 13:51:28 4088 [Note] InnoDB: 128 rollback segment(s) are active.
2015-07-16 13:51:28 4088 [Note] InnoDB: Waiting for purge to start
2015-07-16 13:51:28 4088 [Note] InnoDB: 5.6.24 started; log sequence number 1666100
2015-07-16 13:51:28 4088 [Note] Server hostname (bind-address): '*'; port: 3306
2015-07-16 13:51:28 4088 [Note] IPv6 is available.
2015-07-16 13:51:28 4088 [Note]   - '::' resolves to '::';
2015-07-16 13:51:28 4088 [Note] Server socket created on IP: '::'.
2015-07-16 13:51:28 4088 [Note] Event Scheduler: Loaded 0 events
2015-07-16 13:51:28 4088 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.24'  socket: ''  port: 3306  MySQL Community Server (GPL)


texts: Are there any in-memory hybrid databases available?

texts:
I am looking for a database that is -

Relational (Not NoSQL).
Has an in-memory store.
Has disk-based backing storage.

I want to set up a system where entire contents of the disk are replicated in memory. So writes are made to both the in-memory store and disk, but reads are done only against memory.

Most of the databases I have seen such as H2  or HSQLDB offer in memory grid or disk based tables. I am looking for an in-memory grid WITH disk based backing storage.

Is there such a database system available or is it possible to configure MySQL (or some other) database to work in such a manner?


texts: What are the things to looks for or capture when there are blockages in Sybase

texts:
Currently i look for SPID which are blocking other spid's,enable traceon(3604) to capture SQLTEXT, Showplan.

If blockages keep re-occuring over time especially when certain query is executed, what are the next steps forward?

Thanks


texts:
I received a database with a few million records in it, but apperently there might be duplicate records in them.

A user enters data into the database and a primary key is generated, however if the user enters the same data again, a new primary key will be generated for that data, even though the data has already been entered before. There are no checks on this.

I need to go looking for these duplicates, but I do not really know where to start. I first thought concatenating all cells except the primary key in a subquery and then count these rows and see which ones have a count higher than 1.

cfr.

pkey    recipe     fkey    comment
1   toast       3       tasty
2   curry       2       spicy
3   curry       2       spicy
4   bread       1           crumbly
5   orios       2       cookies

Here the curry entries are identical and I'd have to delete 1 of those.

However I read concatenating is unpredictable in mysql and it just feels a bit wrong to me as well.

Any hints ?


texts: Backing up a 400-500 GB database on MySQL/Amazon RDS?

texts:
We have an Amazon RDS small instance for a MySQL datawarehousing database.
Database size will be around 400-500 GB very shortly.
What is the best way to backup our database?

Can it be done efficiently with automatic backups and DB snapshots available with RDS?

Or should we consider some other options? What would those be?
Using EC2, hosting in some other cloud, or using any other database?


texts: Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?

texts:
I have a problem with opening Large Object in postgresql version 9.2,

select * from pg_largeobject where loid = '19423';

will return data like this:

loid oid;page no integer;data bytea
19423;0;"<Comment>ripple height</Comment><Units>meter</Units><Model>TRIM2</Model><Title>Results of TRIM model (Hartmut Kapitza): bottom temperature, bottom salinity, bed shear stress generated by currents. Results of WAM model (Heinz Guenther, Ralf Weisse): bed she (...)"

However, when I try to open with

select lo_open(19423, x'40000'::int);

It returns error:

ERROR: large object 19423 does not exist
SQL state: 42704

The result of this query:

select * from pg_largeobject_metadata where oid = 19423

is empty: lomowner oid; lomacl aclitem[]

What is this kind of error in Postgresql? I have no clue about it. The data was restored from 1 postgresql dump .sql file.


texts: What are best practices in designing tables for dynamic mathematical inputs?

texts:
I am taking a first stab in designing a system that gives ratings to our assets based on various criteria.
So to simplify what I am doing - it will be this times 10...
I have an asset tables (docs/video/webpage) and we will give it a rating based on:

Last date it was updated.
Number of hits/downloads.
The number of tags associated with it.

All three of these criteria are stored in that table (well tags are stored as comma seperated associates).
We want to have tiers of weighting based on the values above.
An example is:

Date - Between 0-30 days 10 points, between 30-90 days 5 points, over 90 0 points.
Hits - Between 0-5000 hits 0 points, between 5k-10k hits 5 points, over 10k 10 points.
Tags - Between 0-5 tags 0 points, between 6-10 tags 5 points, over 10 tags is 10 points.

Then we add up the points for each asset and give it a number.
I have already started doing this just using a simple mysql view and a TON of if/then statements.   It works but it is really sloppy and it does not anyone else to edit the criteria or values.
Since I have never designed anything with a dynamic amount of criteria - so for instance changing the number of hits for the various tiers or changing the points for each tier or even changing the number of tiers - I figured I would ask what are the best practices in setting up tables and relationships to do this most efficiently.


texts: How to model a table in a relational database where all attributes are foreign keys to another table?

texts:
I've been working on the data model for a healthcare mobile app that will collect the user's symptoms and will book an appointment with a doctor if requested by the user.
I was thinking the app would list the available symptoms and the patient could choose from them, and then the system would estimate some illness based on the chosen symptoms and perhaps this Illness record could be a foreign key in the Diagnosis table. Then, when booking an appointment, there would be a foreign key to this Diagnosis record in the Appointments table. Later, this Diagnosis tuple could be updated by the doctor during the appointment. The problem with this approach is that this way in the Diagnosis table we are only storing some estimated illness instead of the actual symptoms of the patient that were present when booking the appointment.
I'm having trouble figuring out the model for the Diagnosis table, and also what to include in the Appointments table (besides doctor name, appointment date etc. which are not relevant to the question) regarding the diagnosis provided by the app.
What do you suggest should constitute the Diagnosis table? Should I store the symptoms of the patient instead of the estimated illness and refer to this in the Appointments table rather than some illness which later might get overwritten by a doctor anyway? If so, this would mean that we don't know how many attributes (symptoms) one record of the Diagnosis table could have, but they would all be just foreign keys to the Symptoms table.
Also, do you think I can model and do this in a relational database or should I be thinking of a NoSQL database?
I'm only listing the tables that I think are relevant to the question:
symptoms:            illnesses:                                 diagnosis:         appointments:

id    name           id    name               FK_symptoms?      id    ?    ?       id    FK_patient_name     FK_diagnosis
1     fever          1     pneumonia                            1                  1
2     chills         2     allergic rhinitis                    2                  2
3     sore throat    3     asthma                               ...                ...
4     headache       ...
...

What other approach would you suggest?


texts:
Hi I get this error when connecting to SQL Server remotely from a website.
I checked the SQL Server logs and get the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

The SQL Service account is running as LocalSystem. From what I have read, SQL Server service automatically adds this account to SPN on startup.

TCP/IP is also enabled on the SQL Server.

Any ideas on what I need to do? Thanks!


texts: Auto-Shrinking All Databases Files To Their Minimum Size In Sql Server

texts: I restore my all databases in production to my development environment monthly. Development server has 4 TB disk space and there are 4 different instances over it which containt databases on the production server. In Development, i can truncate and shrink some big-sized tables to expand free disk space. The purpose of doing this process is the complaint of software developers about the development server slowness. I talked to system administrator team and they informed me that the slowness was because of the insufficient disk space area. So, i make this work. My question is about how i can shrink all database files to their minimum size after truncating related tables. So, should i make it with powershell, and how?


texts: Performance of checking for data in related tables

texts:
NB: this question is purely academic / to help improve my understanding of SQL Server performance.

Given a master table which relates to one or more other tables, how would you determine the best approach to querying that master table for records, which include an indicator to the presence of records in the related tables?

For example, say we have a Person table and wanted to get a list of all people along with an indicator of whether they have children (in this example Person can be reused as the related table):

create table Person
(
    Id bigint not null constraint pk_Person primary key clustered
    , ParentId bigint null constraint fk_Person_Parent foreign key references Person(Id)
    , FirstName nvarchar(256) not null
    , LastName nvarchar(256) not null
)

We could run any of the queries below to check for the presence of related children:

--variables for restricting our result set, just to keep things interesting
declare @LastName nvarchar(256) = 'Be%'
, @FirstName nvarchar(256) = null

example 1

--  fairly straight forward, but requires grouping to account for the
--  potential of a parent having multiple kids (which I don't care about here)
--  which could be adding some inefficiency.
select parent.Id
, parent.FirstName
, parent.LastName
, case when max(child.Id) is null then 0 else 1 end HasChildren
from Person parent
left outer join Person child --1:n
on child.ParentId = parent.Id
where (@LastName is null or parent.LastName like @LastName)
and (@FirstName is null or parent.FirstName like @FirstName)
group by parent.Id, parent.FirstName, parent.LastName --resolve 1:n

example 2

--  avoid the need to group the results by first getting
--  a single child per parent.
--  may be inefficient because we get children for all parents
--  even if we filter for only a few parents.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce(child.hasChildren, 0) HasChildren
from Person parent
left outer join --1:? (0 or 1)
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId is not null --not sure if this adds value
) child
on child.ParentId = parent.Id
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)
--group by removed since we're 1:?

example 3

--  same as #2 except we limit the child results to those
--  related to the parents we're interested in / having stored
--  them in a CTE to avoid querying for the same parent data
--  in the inner query and outer query.
--  Getting a bit silly now, but could overcome some inefficienies?
;with parentCTE as (
    select Id, FirstName, LastName
    from person
    where (@LastName is null or LastName like @LastName)
    and (@FirstName is null or FirstName like @FirstName)
)
select parentCTE.Id
, parentCTE.FirstName
, parentCTE.LastName
, coalesce(child.hasChildren, 0) HasChildren
from parentCTE
left outer join --1:? (0 or 1)
(
    select distinct parentId, 1 hasChildren
    from Person
    where parentId in --reduce the amount of data we return here based on the records we're interested in
    (
        select Id
        from parentCTE
    )
) child
on child.ParentId = parentCTE.Id

example 4

--  back to a simple one; just check for children on our parents
--  but this time having brought back the full parent set.
--  may be inefficient because we're querying the table once per
--  matching parent to check for children.
select parent.Id
, parent.FirstName
, parent.LastName
, coalesce((select top 1 1 from Person child where child.parentId = parent.Id),0) HasChildren
from Person parent
where (@LastName is null or LastName like @LastName)
and (@FirstName is null or FirstName like @FirstName)

I'm after information on how to better understand the trade-offs involved in such situations as opposed to a simple example 3's best. Pointers to articles which may help me understand would also be welcome.

Related SQL Fiddle: http://sqlfiddle.com/#!6/edc17/3


texts: Import Oracle full dump file to fresh Oracle installation

texts:
I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64.

I want to import the file to a clean database. However if I don't create a database first, there's nothing to create. If I create the database, it throws a lot of errors complaining of missing users and tablespaces, even with FULL=Y.

How can I import this dump into a clean Oracle Install?

The export ran under system and the import also. The index file does not contain the statements to import the users and tablespaces, however when I run the full import on the original file, IMP tries to create the TABLESPACES (which have incorrect paths, which I have to change, and i know that) but it does not try to create the users, only complaining they don't exist.

The actual export command user is:

exp <a_User>/<a_Password> FULL=Y FILE=/PATHHERE/oradata/Export/expdat.dmp log=exp`date + %d%m%y.%H%M%S`.log consistent=y


texts: How can I disable database lock in sqlite with python?

texts:
I made a application in python and it should read and write simultaneously in multiple instances(multi CMD widows)(multi-threading). each thread is configured independently so it will not change the data that written with other instances. so in action app instance 1 will work with John's data and app instance 2 will work with Jane's data and so On.

the application concept is simple enough but in action when running multiple instances at once, there may be a situation that I get database locked error and the app will stop working.

I want to disable this locking system, because its not necessary in my application, I designed it Independent just for this reason.

can anybody tell me to do it in python? is it possible? where should I place a code or sql command or anything?


texts: Select IDS where all values can't be found in another table

texts:
Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve

I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.

DB looks like:

Table 1

BOOK ID| User ID
1      |   1
1      |   2
2      |   17
2      |   16
3      |   17
3      |   16
4      |   3
4      |   16

Table 2

USER ID | Name
   1    | Jeff
   2    | Bill
   3    | Fred

The desired outcome would be to bring up Book IDs 2 and 3 as both IDs only have users that cannot be found in Table 2.


texts: Query to change 2 statements into a batching job

texts:
I have a SQL server agent job setup to delete records from a parent table where the date is greater than 2 weeks and also delete all records from a child table where the corresponding ID no longer exists in the parent table (basically a FK relationship without the actual relationship).

The issue is that even though this job takes under a minute to run it is being deadlocked every other week.  The code you see below is my attempt to setup batching to break it up.  My issue right now is that the entire statement completes successfully, but for whatever reason no records are being deleted from the parent table despite it having a very simple WHERE class (greater than 2 weeks).  It is almost like it is completing the first loop, going to the second, but doing nothing, and then saying it completed.  I am sure I am missing something very simple that is causing me to overlook this issue.

SET NOCOUNT ON

USE database
GO

DECLARE
    @sql nvarchar(1000)
    ,@i int
    ,@ii int
    ,@batchSize int
    ,@totalDeleteLimit int
    ,@totalDeleteCurrentCount int
    ,@totalDeleteCurrentCount2 int
    ,@interval nvarchar(100)

SELECT
    @i = 1
    , @ii = 1
    , @batchSize = 10000
    , @totalDeleteLimit = 2000000
    , @totalDeleteCurrentCount = 0
    ,@totalDeleteCurrentCount2 = 0
    , @interval = '00:00:00.01'

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100)) + N' - Start Logging Purge'

BEGIN TRY
WHILE @i <> 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N')
    FROM ChildTable WHERE LogID IN (SELECT LogID
                FROM ParentTable
                WHERE [Timestamp] <= dateadd(week,-2,getdate()))'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @i = @@ROWCOUNT

    --print @i
    SELECT @totalDeleteCurrentCount = @totalDeleteCurrentCount + @i;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
WHILE @ii <> 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N')
    FROM ParentTable
    WHERE [Timestamp] <= dateadd(week,-2,getdate())'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @ii = @@ROWCOUNT

    --print @ii
    SELECT @totalDeleteCurrentCount2 = @totalDeleteCurrentCount2 + @ii;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount2 >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached';
        BREAK;
    END
    ELSE
        CONTINUE;
END
END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return
    -- error information about the original error that
    -- caused execution to jump to the CATCH block.
    RAISERROR (
        @ErrorMessage, -- Message text.
        @ErrorSeverity, -- Severity.
        @ErrorState -- State.
    );
END CATCH

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100))  + N' -- Total Purged: ' + CAST(@totalDeleteCurrentCount as nvarchar(20))


texts: CATCH block is triggered when it should not

texts:
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of the question, but it is because of it I wrote the code below.

Consider the following complete example with nested error handlers:

begin try

  begin try
    select 'Step 1';
  end try
  begin catch
    select 'Step 1 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

  begin try
    select 'Step 2';
    raiserror('Step 2 error', 16, 1);
  end try
  begin catch
    select 'Step 2 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

end try
begin catch
  select 'Outer handler - handling ''' + error_message() + '''';
  goto commit_and_exit;
end catch

commit_and_exit:

raiserror('Error raised for the caller to see', 16, 1);

It is documented that

  GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

Or can it?

Given the code above, a sane programmer would figure the output will be

  Step 1
  Step 2
  Step 2 handler - handling 'Step 2 error'
  <Error raised for the caller to see>

In fact what is happening is:

  Step 1
  Step 2
  Step 2 handler - handling 'Step 2 error'
  Outer handler - handling 'Error raised for the caller to see'
  <Error raised for the caller to see>

When debugging step by step, I can see that control leaves the try/catch blocks completely, then an error is raised, the control is returned to the outermost catch block, that block executes, the control goes to commit_and_exit: again, and that final block is executed again.

If you have some commit trans or rollback transs under commit_and_exit:, you are going to attempt to commit the tran twice. With imaginable results, given the fact there can be outer transactions started by the caller.

I also tried create another label, end_of_outer:, right before the outer end try, so that the control leaves the outer try block "normally". Interesting enough, that did not make any difference.

What the hell is going on, and what is the correct way of doing it?


texts: Oracle 11g production database copy to USB drive

texts:
What is the correct commands for backing up a production Oracle 11g database (while still running) to an external USB drive or different directory.

I am new to Oracle and but have experience with SQL Server.  Essentially, I am looking for the equivalent of a COPY_ONLY backup - something which will not interfere with the existing backup process.

The goal is to take the backup offsite and use it to develop migration scripts for a new application.  Any insights would be appreciated.


texts: Cant start mysql and cant find mysql.sock

texts:
I am on Cento 6.9 and trying to solve a problem with Mysql.

When I try to start mysql I get:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket
  '/var/lib/mysql/mysql.sock' (2)

As per recommendation of gurus here I tried to find mysql.sock file. I have tried:

find . -name mysql.sock

But could not find anything. Any idea where my mysql.sock is?

I have also tried:

lsof -U -c mysqld -a

which returns nothing.


texts: Why does the plan cache contain estimated and not actual execution plans?

texts: I can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.


texts: How to migrate Oracle database to MySQL server?

texts:
Currently our application is running using Oracle 11g database. We are developing newer version of the application which runs on MySQL cluster. We need to migrate the data from Oracle database to MySQL database. This is not a direct copy of database objects. In newer version of the application schema(table structure and relationships) is changed. And audit tables also needs to be copied. Is there any way to map tables, columns from old Oracle schema and new MySQL schema and apply copy so that the data will be copied?

We are thinking doing this activity in two steps.
1. Convert Oracle database to MySQL database.
2. Writing stored procedure to copy table by table.

Is there any best practices while performing this kind of database migration from one RDBMS vendor to another? Is there any open source or commercial tools available for this?


texts: sqlservr.exe uses lots of CPU, while SQL queries does not seem to use much CPU at all

texts:
I've found a Query which supposedely should determine how much CPU is being used by SQL queries, system idle and other processes. This is the Query:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
where SystemIdle >= 80
ORDER BY record_id DESC;

This shows that the CPU under the column SQL Server Process CPU Utilization is rather stable at around 0-10%.

When I'm checking the process task at the server, however, I can see that the CPU usage is very high at 90-100% from time to time. When I check what processes are using the most CPU in the task manager, I can conclude that sqlservr.exe uses much more CPU than the other processes (75+%).

In fact, there are more than one sqlservr.exe present.

My question is, really, how can the SQL queries use Little CPU, while sqlservr.exe uses very much?

I have some queries running in the background, by for instance using SQL jobs, but these should be captured in the CPU Query.


texts: Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

texts:
I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink

Oracle throws error :

  select * from "test_table"@dbLink
  Query Execution started at Thu Mar 08 17:57:50 IST 2012
  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  ORA-02063: preceding line from DBLINK
  ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], []`

On further analysis I realized this happens only for tables which have TEXTIMAGE_ON [Primary] (Specifying that text, ntext, and image columns are stored on PRIMARY filegroup)

Then I went over the create command for this table which is

CREATE TABLE [dbo].[test_table](
[ADJ_ID] [numeric](19, 6) NULL,
[ADJ_TYPE] [numeric](19, 6) NULL,
[PK_HASH] [nvarchar](255) NULL,
[WH_HASH] [nvarchar](255) NULL,
[ADJ_REVISION] [numeric](19, 6) NULL,
[LAST_MODIFIED_BY] [nvarchar](255) NULL,
[LAST_MODIFIED_DATE] [datetime] NULL,
[COMMENTFIELD] [nvarchar](2000) NULL,
[DATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The same error is not thrown for tables which do not have filegroup specified as PRIMARY or if I have the same table without clause TEXTIMAGE_ON [PRIMARY]

Can any one suggest a solution to overcome this error?

Thanks

The trace content is

` 1.exec sp_unprepare 1

2.SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM "test_table" "A1"

3.declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N' SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM  "test_table" "A1"',1
select @p1`

Thanks.