texts: how to find a connection string value for a session in sql server?

texts:
I know there was a similar question before at SO as shown here
However, that answer really does not address my question.
So here is my scenario, and I will demo it with PowerShell.

The following code will create two sessions on my local sql2016 instance

#The only difference in the two connection strings is [pooling] property, the $connstr_1 has pooling=true, while the $connstr_2 has pooling=false.

$connstr_1 = "data source=localhost\sql2016; initial catalog=master; trusted_connection=true; app=ConnPoolTest; pooling=true";
$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($connstr_1)

$connstr_2 = "data source=localhost\sql2016; initial catalog=master; trusted_connection=true; app=ConnPoolTest; pooling=false";
$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection($connstr_2)

$SqlConnection1.Open()

$SqlConnection2.Open();

My question is on sql server side, how can I know which session's connection string has pooling property set to true and which set to false?


texts: SQLAnywhere: Cursor getting closed after executing a DDL statement

texts:
Having this code here:

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

I've recognized, that the cursor is getting closed after the execute(@query) statement. Based on what I've found so far it's because of implicit commit done for DDL statements.
As I need to create number of procedures via a script, is there any work around for this issue?


texts: Building a ColumnStore index hogs system resources

texts: I am experimenting with the columnstore and I am finding that while it is building the process hogs all the system resources. I have a table with 2.3 billion rows and while I build the columnstore on it all other queries come to a complete stand still. Is there a way to limit the amount of resources used by the columnstore build so that my other queries can continue to run. The other queries do not involve this table.


texts:
select *
from
(
    select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster
    where date  between '2016/12/31' and '2017/12/31'
    group by type,month(date)
) src
pivot
(
  sum(amount)
  for date in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;

How can I get zero in null value columns? The above query is returning null's, where there is no data. What changes do I need to make in this to get 0 instead of null?


texts: IORA-00904: Invalid Identifier when converting Radians to Degrees

texts:
I am using this simple query in Oracle SQL Developer to basically convert a value in Radians to Degrees but I get error at the function itself.

SELECT Degrees(1.571) as ANGLE FROM DUAL;

  Error: Ora-00904: "DEGREES": invalid identifier
  00904. 00000 - "%s: invalid identifier"

I checked Oracle documentation and a function called DEGREES does exist. I tried changing the query to convert DEGREES to RADIANS but same error.

Can someone please advise what's wrong here? The database I am connecting to is Oracle 12cR1.

Thanks in advance.


texts: Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

texts:
I have a file /tmp/foo.csv that looks like this:

1,2
10,20
100,200

I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner, and were created by the same user, yet when I run

COPY foo FROM '/tmp/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I get this error

ERROR:  could not open file "/tmp/foo.csv" for reading: No such file or directory

When I run

COPY foo FROM '/mydata/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I see COPY 3.

What am I missing here?

Here's the relevant file and directory information:

╭─foo@bar  / ‹py27›
╰─$ ll -d /mydata /mydata/foo.csv /tmp /tmp/foo.csv                                                                                1 ↵
drwxrwxrwt  2 root root 4.0K Sep  9 08:26  /mydata
drwxrwxrwt 17 root root  420 Sep  9 08:57  /tmp
-rw-r--r--  1 root root   17 Sep  6 12:03  /mydata/foo.csv
-rw-r--r--  1 root root   17 Sep  9 08:36  /tmp/foo.csv
╭─foo@bar  / ‹py27›
╰─$ diff /mydata/foo.csv /tmp/foo.csv
╭─foo@bar  / ‹py27›
╰─$

I'm running Arch Linux

NOTE: I've also tried this on another Linux machine running CentOS and I was unable to reproduce the problem.


texts: ODI remove auto lock when object / datastore is opened

texts:
I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time.
Now I want to remove the automatically lock from GUI but I couldn't seem to find it.
I googled with such topics
https://www.appservgrid.com/documentation111/docs/fmw11g1114documentation/integrate.1111/e12643/appendix_b.htm
https://odielt.wordpress.com/tag/odi-studio-settings/
So I searched userpref.xml inside the hard drive, I do find one userpref.xml.
I opened the file and inside it's just
<?xml version="1.0" encoding="UTF-8" ?>
<UserPreferences xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://www.oracle.com/odi/userprefs UserPref.xsd"
                 xmlns="http://www.oracle.com/odi/userprefs">
  <Preference>
    <AcessibilitySetting>
      <Screenreader>false</Screenreader>
      <HighContrast>false</HighContrast>
      <LargeFonts>false</LargeFonts>
    </AcessibilitySetting>
    <UserName>None</UserName>
    <Repository>
      <Name>None</Name>
      <GUID>None</GUID>
    </Repository>
  </Preference>
</UserPreferences>

What can I do to make it not locking up anymore each time I open an object?  If it can be done through GUI then it's perfect if not, what should I do inside the XML file?
Thanks in advance for any help and advices.


texts: changing a range into discrete values for a field in oracle

texts:
I have a data table that contains records like this:

ZIP       Ground  Air
010-041   005     305
150-163   004     304

As I have it right now, it's actually an excel file but I eventually need to convert it into a table in Oracle.  The problem is that the format of the first column is a range but I need it as discrete values i.e.

ZIP

10    005     305
11    005     305
12    005     305
13    005     305
14....

What's the easiest way to accomplish this in excel and/or oracle?


texts: SQL Server 2012 stored procedure runs very slow sometimes

texts:
I have a stored procedure with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes.

The stored procedure runs most of the time in around 3-4 seconds, but sometimes takes over 10 seconds (there is a mailer setup in the SQL Server for sending emails for queries that take over 10 seconds). It also ran for over 100 seconds a couple of times. How do I troubleshoot this and what could be the possible reasons for the varied execution times?

The query is something like this:

SET NOCOUNT ON;

DECLARE @date2 datetime;
SET @date2 = GETDATE();

WITH lstats
AS
(
    SELECT
        [T1].id1, [T3].flag1, [T3].num1, [T3].string1,
        [T3].date1, [T3].string2, [T3].id
    FROM
        [dbo].[table1] AS [T1]
    INNER JOIN
        [dbo].[table2] AS [T2] ON [T1].id1 = [t2].id1
    INNER JOIN
        [dbo].[tabl3] AS [T3] ON [T2].[id2] = [T3].[id2]
    WHERE
        ([T3].[flag1] = 1 AND [T3].id = @id
         AND (([T3].[num1] IS NULL) OR (-1 <> CAST( [T3].[num1] AS int))))
)
SELECT
    [l].[id1] AS [id1],
    [l].[id] AS [id],
    [lu].[id] AS [idx],
    CAST(CAST([date2] AS datetime2) AS datetime2) AS [date2],
    (SELECT COUNT(1) AS [A1]
     FROM lstats AS [ls]
     WHERE ([l].id1 = [ls].id1)) AS Count1,
    (CASE WHEN (2 = CAST( [Type] AS int))
            THEN
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]
                WHERE ([ls].id1 = [l].id1)
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2))
                  AND (([ls].[string2] IS NULL) OR (NOT ([ls].[string2] LIKE '%sometext%'))))
            ELSE
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]
                WHERE ([ls].id1 = [l].id1)
                  AND (([ls].[string1] IS NULL) OR ('sometext2' <> [ls].[string1]))
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2))
                  AND (([ls].[string2] IS NULL) OR ( NOT ([ls].[string2] LIKE '%sometext%'))))
         END) AS Count2
FROM
    [dbo].[table1] AS [l]
INNER JOIN
    [dbo].[table4] AS [lu] ON [l].[id1] = [lu].[id1]
LEFT OUTER JOIN
    [dbo].[table5] AS [les] ON [l].[id1] = [les].[id1]
LEFT OUTER JOIN
    [dbo].[table6] AS [u] ON [l].[id] = [u].[ID]
LEFT OUTER JOIN
    [dbo].[table7] AS [lt] ON [l].[id3] = [lt].[id3]
WHERE
    ([lu].[id] = @id OR (@someflag = 1 AND [u].ID = @id))


texts: Changing to compatibility level 150 makes SQL Server consume all available memory

texts:
We're moving an existing DB to a new SQL Server 2019 instance.

If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available memory and all queries time out with an error that says

  There is insufficient system memory in resource pool internal to run this query

The RAM is completely consumed until no query can be run (all end with the previous error I've mentioned). We've tried with 16, 32 and 64GB of RAM.

Can anyone give any tips on how to debug this?

Notice that it's not a query issue...just changing the compat level is enough to make SQL start exhausting the memory.

Even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory.


texts:
I do want to create a column which should has check marks and cross marks.

Here is the part of my query:

iif(order_details.order_status = 'Y',char(252),char(251)) as [completed?]

After I execute the query in SSMS, I'm getting outputs like this - 'ü', 'û'

But I'm assuming, if change from current font to Wingdings in SSMS I'll getting check and cross marks and every outputs will be changed in SSMS. But I only want to change the column  completed? .

Here is my result:

Can any one help me to get check marks and a cross marks?


texts: Capturing transactions opened / committed during backup

texts:
I gotta believe there's a way to do this, but not clear how. Don't know PostgreSQL internals well enough, however.

Let's say I start a dump of a database at 11:00, and I know said dump takes 5 minutes to complete.

I have an open transaction (T1) that was created at 10:59 and because [some reason] it doesn't COMMIT till 11:03
A new transaction (T2) is opened and committed at 11:01
Another transaction (T3) is created at 11:03 and COMMITs at 11:04

Is there a relatively straight-forward way to capture T1-T3 and include them in the same archive as all of the other committed changes that occurred between the time of my last backup and 11:00? In essence, I want to "loop back" to capture all the activity that occurred between 11-11:05 and save that, too.


texts: How to create a Relation between a table and a many-to-many relation table

texts:
I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:-

Skill that have predefined levels assigned to it, such as junior, senior, manager.
Staff will be assigned to certain skill’s level.

For example inside ym system i will have such an info; staff A is connected to a skill named “Sql server 20082” through Senior level.

Currently I have set the relationship between the tables as follow:-

But I have this concern, currently I am storing the relation between Skill & level though a many-to-many table (Skilllevel), and I am storing the relation between staff and skill though a many-to-many table (SkillLevelStaff). But on the skilllevelstaff table I am storing the same relation skillId+LevelID , which already defined inside the  SkillLevel table.
So  can anyone advice on this please? should i for example create another system generated ID for the SkillLevel table and setit as the PK instead of the SkillID+LevelID?
Thanks


texts: Postgres configuration for high-latency storage

texts:
I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network and Ceph (~15ms vs <0.1ms for a normal SSD storage) creates a huge bottleneck.

A setting of fsync = off improves performance tremendously, but of course, is not a viable solution since it may create data corruptions in case of power outage, etc. There is also wal_sync_method, but even though changing it improves TPM a bit, its effect are negligible.

How can I configure the Postgres or change the design of my system (e.g. using different storage for WAL and data) to improve performance?


texts:
I have the following scenario a user belongs to several Groups. The Groups are organized in a hierachy:

I want to aggregate all the Person Information of the Groups the User Belongs to. Eg. Person 1 belongs to Group 7. And Group 7 has connections to the following Groups 1,5,7,8 (complete branch)

I wrote the following query:

SELECT person.id, array_agg(info)
FROM person LEFT JOIN person_group
ON ARRAY[person.id] && path
LEFT JOIN person_information ON
ARRAY[person_information.id] && path
GROUP BY person.id;

Problem with this query is that I get duplicated Information:

I don't want to use UNIQUE on the array_agg(info) field. Rather I would like write my join so that I only get UNIQUE rows of the Person Information Table.

*How would I do that?*Thank you


texts: JDBC Connection on Current Primary Database After Switchover: ORA-01033 encountered

texts:
We are using Oracle Data Guard and currently testing Disaster Recovery scenarios, which is starting the application after switching over from the Primary Database to the Secondary Database.

I am using the following connection string as per Oracle Documentation:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD_DB))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DR_DB))
    )

)

The scenario is that we are now switched over to the secondary database, so the application should now connect to DR_DB. However, when we try to connect, it still connects to the PROD_DB, because we're getting the following exception:

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

This may be answered already in some threads, and I may not have searched hard enough, but I also do not know if I am expecting the correct result.

I do not expect the application's database connection to stay alive after a switch over; I only want to avoid reconfiguring the application whenever we do DR, and I expect that the above connection string will connect to whichever is the primary database. Is this correct?

Thank you guys for your help.


texts: I have a basic Java program which can list data from Berkeley DB database. The question is how I can list the database scheme to see what object are available? Unfortunately I don't have information what is the design of the database.


texts: Partial backup restore of read-only file group on new database

texts:
Let's say, I have a database myModel2015 that contains two file groups - Primary A and Secondary Read-Only B. Then, I am creating a partial backup of the secondary file group.

Now, let's create a new myModel database (there might be difference in the database objects in the primary file group, but the tables in the read-only file group will be the same as this in the myModel2015 database).

Is it possible to perform piecemeal restore of the Secondary Read-Only B file group but using the new database?


texts: How do you list all items from all databases located in a specific tablespace in postgresql?

texts:
I'm trying to clean out some old tablespaces in my postgres instance, and I can't seem to figure out how to list everything in a specific tablespace.

SELECT * from pg_tables WHERE tablespace ='cold_storage' returns items from the current database in the tablespace, but short of iterating over every database and querying within that database for the tablespace contents, I haven't figured out how to do this.

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.


texts: Comparing table structure and contents using pg_dump output from two different databases

texts: If I have the output of pg_dump from two different databases, how can I get the difference in structure and contents of all the tables between these two databases?


texts:
I am trying to free up some space used by a MySQL instance, but I know pretty much nothing about MySQL.

I notice that in the mysql\data folder there are lots of localhost-bin.0001** files, as illustrated in this screen shot:

What are these? Can they be backed up/moved/deleted?

Edit

It seems from look here that these files are log files and can be deleted.

The post mentions:

As long as you do not have mysql replication installed, then [you can delete the files].

How do I check this?


texts: Is there any way to tell which derived column failed?

texts:
In an SSIS dataflow task we have a derived column transformation with approximately 100 columns (basically converting raw input string data into typed variables). When this task fails, is there any way to tell which column caused the failure, for logging purposes? The only other alternatives I can think of are a custom script task to perform each conversion individually (yuck) or a separate derived column transformation for each data point (double yuck).

Basically I just want to be able to re-direct failure rows and know why they failed.

So an example. Our package is being used to allow users to bulk-upload to our database using Excel spreadsheets. So let's say the spread sheet coming in looks like this (except there's hundreds of columns):

+--------+-----------------+---------+------------+---------+
| Text1  |     Number1     | Number2 | DateTime1  |  Text2  |
+--------+-----------------+---------+------------+---------+
| Spring | 1               |       1 | 1/1/0001   | Flowers |
| Summer | 2               |       2 | 6/1/2015   | Sweaty  |
| Fall   | N/A             |       3 | 10/31/2099 | Crunchy |
| Winter | This is garbage |       4 | 12/12/2020 | Icy     |
+--------+-----------------+---------+------------+---------+

In this instance we want Spring, Summer, and Fall to succeed. Fall with have a null value for Number1. The derived column will have logic that looks something like this (not valid syntax, just the logic)

sanitizedNumber1 = Number1 == "N/A" ? null : cast(Number1 as int)

Winter will be redirected down the error path and logged. Is there any way to know which derived column failed? Again, we have about 100 inputs that are being processed in this transformation in a similar fashion. I'd like to be able to log something like:

  Import record "Winter" failed due to invalid data in "Number1"

It doesn't necessarily have to be this format, but anything that would allow a user to be able to uniquely identify the bad data point would be acceptable. I know this would be possible using a script component and performing the conversions manually (which is what we're going to have to do if there's no better option) but if it's possible to just modify the Derived Column Transformation to provide something along these lines I'd rather do that instead of re-implementing the entire component in a script.


texts: Why does pg_basebackup use so much less resources than pg_dump?

texts:
When I run pg_dump the main application starts to lag out and take 1 to 2 seconds for even the simplest queries on primary keys of small tables. When I run pg_basebackup it's unnoticable. Why is this?

I know there are many questions on stackexchange about why pg_dump is slow, that's not what I'm asking; I'm asking what pg_basebackup does differently to make it unnoticable. And ideally is there any way to make pg_dump run "in the same way" so that I can use it without messing up the application?


texts: Which MySQL queries are causing this error message?

texts:
I found this Stack Overflow Q & A:

mysql: see all open connections to a given database?

But by the moments that is the limit of connections, I have not access to my server, which displays error messages:

ERROR: Zend_Db_Adapter_Exception: SQLSTATE[08004] [1040]
 Too many connections (Abstract.php:144)

ERROR: PleskDBException: Unable to connect to database:
 mysql_connect(): Too many connections (Error code: 1040) (Abstract.php:69)

How to know which queries were being made at the time that my server was unavailable?

I have a single site on the server and does not have much movement. There is something causing numerous unnecessary processes and I need to figure out what it is.


texts:
When I run the report for a few days then its good. but if I try to retrive data for more then a year then I get this error message.

I tried to run DBCC CHECKDB as it says in the error, but after 10 min running a error came up that it can't continue.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:952663; actual 2668:-1594740640).
It occurred during a read of page (1:952663) in database ID 5 at offset 0x000001d12ae000 in file 'F:\Data\PointOfSale.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


texts:
I need to set the time of a timestamp in SQL.
My results need to be between yesterday at 7 o'clock and today at 7 o'clock.

Here is the query

WHERE created_at BETWEEN
    ?::timestamp - INTERVAL '1 day' AND ?::timestamp
ORDER BY created_at DESC

The value is:

2017-05-30

I've seen the PostgreSQL datetime functions. But I don't know how to specify a time

Here is the query with static datetimes

SELECT
    *
FROM
    "myTable"
WHERE
    created_at BETWEEN '2017-05-30 07:00:00' AND '2017-05-30 07:00:00'
ORDER BY
    created_at DESC

Now I need to replace the datetime strings with a date string and add the time in SQL


texts: SQL Query Against Previous SQL Query Results

texts:
Is there an SQL database (for C#) that allows me to query against on-disk database and then later on (probably offline at client side) query against the in-memory result set using SQL?

I basically want to always be able to SQL query against whatever comes from a previous SQL query whether on-disk or in-memory.

I'm talking about CRUD queries only.

Thanks!

Background:

This will be used for general adhoc client-side queries at run-time against previous query results and it is also suitable for caching purposes but that is not the main concern here. And yes I need SQL or TSQL.

In your opinion guys what is the performance cost to save a DataSet(s) into an in memory database (say embedded SQL Server) for this purpose?


texts:
Could you help me understand why the schedule on the picture is view serializable without any "blind write"?

Also, should the results be the same if two schedules are view equivalent?

I did create serial version of the schedule and compare but they do not seem view equivalent to each other.

Could you tell me if a concurrent schedule is view serializable,  would it and a serial version of it have the same result in the end?

Me and my lecturer have different opinions, and it is very hard for me to understand hers. She thinks it is view serializable but I do not. I am not sure who is correct.

Definitions

From GeekForGeeks:

  If two schedules are view equivalent to each other, they are called as being view serializable.

  Initial Read If a transaction T1 reading data item A from initial database in S1 then in S2 also T1 should read A from initial database.
  Updated Read If Ti is reading A which is updated by Tj in S1 then in S2 also Ti should read A which is updated by Tj.
  Final Write operation If a transaction T1 updated A at last in S1, then in S2 also T1 should perform final write operations.

  Initial Read If a transaction T1 reading data item A from initial database in S1 then in S2 also T1 should read A from initial database.
  Updated Read If Ti is reading A which is updated by Tj in S1 then in S2 also Ti should read A which is updated by Tj.
  Final Write operation If a transaction T1 updated A at last in S1, then in S2 also T1 should perform final write operations.

  If this three conditions are satisfied, these two schedule are called view equivalent to each other.


texts: How can I use package classes and call packages from Oracle11gr2 stored procedure in certain schema?

texts:
When a stored procedure begins with

4 create or replace procedure user0.import
5 is
6   f1 utl_file.file_type;

compiler throws an error Error(6,6): PLS-00201: identifier 'UTL_FILE' must be declared.

But if I replaced is with declare and ran the same as anonymous block selecting everything after line 4, it would work fine and import data as expected. This is runnning under SYS AS SYSDBA. Also it would compile fine if user0 schema was not used.

Why cannot I as sysdba compile a procedure in a user0 schema?


texts:
I have two tables, PROJECT and BILLINGS, as follows:

Project_ID  Project_Client
----------  ------------------
P1          ABC Company
P2          DEF Company
P3          GHI Company

Proj_ID     Bill_Date   Bill_Amount
----------  ----------- -----------
P1          01-01-2013  123.45
P1          11-01-2013  234.56
P2          08-15-2013  345.67
P2          09-15-2013  456.78
P3          07-31-2013  567.89

I want to return the list of Projects and Project Clients only for projects that have billings AFTER 09-01-2013. How do I do the proper JOIN to do this?


texts: Is there any way to use "like" operator during oracle table partitioning

texts:
I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me.

In my table one column is URI, and for that column I want to use the like operator during partition so that it can help during insertion, because it contains different type of uris such as model://a/b or list://a/b.

I want something like:

CREATE TABLE table_name (
    URI varchar2(20);
)
PARTITION BY LIST ( URI )
      (PARTITION part_mod VALUES (like 'model://%'),
       PARTITION part_list VALUES (like 'list://%'),
       PARTITION part_def VALUES (default)
       );

I tried using Virtual Column-Based Partitioning but didn't get any success. Partitions were created, but during insertion no data is going into corresponding partitions; everything is going into part_def.

So is it possible to use the like operator to partition a table in oracle database?


texts:
I have the table below with some basic information with two defaulted columns

CREATE TABLE [dbo].[INSPECTION](
    [InspectionID] [int] IDENTITY(1,1) NOT NULL,
    [LocationID] [int] NOT NULL,
    [CurrentStateTypeID] [int] NOT NULL CONSTRAINT [CurrentStateTypeDefault]  DEFAULT ((0)),
    [DState] [int] NOT NULL CONSTRAINT [DStateDefault]  DEFAULT ((9)))

When I try to insert a record with incomplete information into this table however, I'm getting an error

SQL for INSERT: INSERT INTO INSPECTION ([LocationID]) VALUES (1);

Error:

  Cannot insert the value NULL into column 'CurrentStateTypeID', table
  'DatabaseName.dbo.Inspection'; column does not allow nulls. UPDATE fails.

I would think with the default, I would get a record with an Inspection of the next number (from the Identity), a LocationID of 1, a CurrentStateTypeID of 0, and a DState of 9.

There is also a trigger on the table which works with the CurrentStateTypeID after Insert, but I would think that would happen after the insert and it's defaulted values.

Trigger Code:

CREATE TRIGGER [dbo].[StateTypeUpdate] ON [dbo].[INSPECTION]
FOR INSERT, UPDATE
AS
IF UPDATE(CurrentStateHistoryID)
BEGIN
   DECLARE @inspectionId int
   DECLARE @stateHistoryId int
   DECLARE @InspectionIDString varchar(max)
   SET @inspectionId = (SELECT InspectionId FROM Inserted)
   SET @stateHistoryId = (SELECT CurrentStateHistoryId FROM Inserted)
   SET @InspectionIDString = CONVERT(varchar(25), @inspectionId)
   EXEC WritePENGSHistoryRecord @stateHistoryId, @InspectionIDString
END
END


texts: Can I read data from a 'hash field' in a legacy Ingres database?

texts:
I have been asked to read and present data from the 'hash fields' of an Ingres 2.0 database, which are apparently multiple pieces of data stored together in a single field in various combinations. I know nothing about Ingres 2 and there is no documentation that I've been able to find. Apparently because the tables aren't indexed or some such the only way to get the data I need to present is from these hash fields.

Everyone at the client company appears to be working under the assumption that reading the data out of these fields is possible, but they do not know HOW this is to be done. I've been given information about which field stores which combination of data, but nothing about how it is encoded (for lack of a better word). Its like staring at a black box. My real concern is that the names suggests that the data is hashed, and therefore non-reversible by design.

Does anyone have any insights that could shed some light on this?


texts: SQL Server 2008: Row count for each hour in a Day and Row count for each day in a Month

texts:
I am trying to get row count in a given week for each day as below, which works fine

SELECT
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Sunday' THEN 1 END) AS S,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Monday' THEN 1 END) AS M,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Tuesday' THEN 1 END) AS T,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Wednesday' THEN 1 END) AS W,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Thursday' THEN 1 END) AS R,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Friday' THEN 1 END) AS F,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Saturday' THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM PARCEL
WHERE [created_at] BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */

I am also trying to get row count for each hour in a given day as below

Hour1 Hour2 Hour3 Hour4 .............Hour24  Total
 33    544   55     77                 23    13444

Also row count for each day in a given month as below

 Day1  Day2  Day3   Day4 .............Day31  Total
 33    544   55     77                 23    13444

I am pretty new to sql programming, am not sure where to start on this for tha lateral 2 reports, can someone please help me with this?


texts:
I'm converting a database originally written in MS Access VBA to .net and many of the SQL statements that worked in Access don't work in .net.  My data set is standard stock market daily information with columns for Date, Open, High, Low, Close, and AdjustedClose.  I want to aggregate the daily data by week (WeekEnding).  In Access I was able to add to a Weekly Table, calculate the WeekEnding date and aggregate the data in a single query:

SELECT (WeekEnding Calc) as WeekEnding, Max(High), Min(Low), First(Close)

Calculating the Week Ending date within a SQL statement seems to be a non-starter in Visual Studio so I added a column for the Week Ending date and calculated that separately.  The First statement worked since the daily data was sorted in descending order by date but is not an option now.  What I'm looking for is something like:

SELECT WeekEnding, Max(High), Min(Low), ????(Close)
FROM DailyTable
GROUP BY WeekEnding

I haven't been able to figure out how to get the value of (Close) based on the maximum DailyDate within each group.  I'm sure this has been solved for many times in the past and would appreciate any help.


texts:
In my environment, there are servers running on native backup and Ola Hallengren plans. Our servers are a combination of 2008, 2012 and 2014. All the full backups are taken at 12am and log backups are taken every 15 minutes.

I have never accounted for Daylight Saving time before, so please tell me what adjustments I should make.

Will the 12am full backups be affected and what happens to the log backups?


texts: Full text search of values with a special character (') produces unexpected results

texts:
I'm having a problem with MySQL full text search. I have a users table that has a single column, username.

The problem appears when I'm trying to search for a user with a special character (') in its name. Let's say the name is Dan v׳ila, when searching for "dan" alone it finds the result but as soon as I'm searching for "dan v" it won't show the name with the ' in it.

Examples of queries follow:

Returns the result:

Select * from uses
Match(username) against('+dan*' in boolean mode)

Doesn't return the result:

Select * from users
Match(username) against ('+dan* +v*' in boolean mode)

It is worth noting that names without ' are showing in both cases.


texts: in-place upgrade native SSRS without the SQL Server portion

texts:
We have a single server (2012R2) running SQL 2008R2 + reporting services native mode 2008R2
Can the reporting services portion be upgraded alone without the database engine?
Or can the SSRS db be migrated off, SQL instance shut down and then SSRS alone upgraded?


texts:
I need to issue a query against DBs on two servers:

[CRM].[CRM].[dbo].[BasicInfo] (NationalID PK, CustomerCardNo)
[ERP].[ERP].[dbo].[Order] (OrderID PK, CustomerCardNo)

And what I want to do is like this:

select *
from   [ERP].[ERP].[dbo].[Order] as O
where  O.CustomerCardNo in (select CustomerCardNo
                            from   [CRM].[CRM].[dbo].[BasicInfo]);

I think there are three ways to achieve this:

linked server: my manager would probably not authorize this because they are not familiar with linked servers;
open data source: company policies do not allow developers to have access to DB passwords;
SSIS: seems to be the only choice left, and my company adopts SSIS widely.

When I googled I had no luck finding tutorials regarding how to use SSIS to do this. The closest question is this. Please provide me with some guidance.


texts: How do I add a partition to an existing table in mariadb / mysql?

texts:
I have the following table.

I want to add partitions too.

CREATE TABLE `app_log_Test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `version` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `app_log_dateCreated` (`dateCreated`),
  KEY `app_log_label` (`label`),
  KEY `app_log_event` (`event`),
  KEY `app_log_level` (`level`)
) ENGINE=TokuDB `COMPRESSION`=tokudb_quicklz

I am using MariaDB 10.

MariaDB [test2]> alter table app_log_Test partition by RANGE(TO_DAYS(dateCreated))(
-> PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')) ENGINE = TokuDB,
-> PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')) ENGINE = TokuDB);

I get the following error

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function


texts: Why is a new user allowed to create a table?

texts:
I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:

postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           |
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

So far so good. Now I create a user:

postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER

Okay. When I try to connect to the database, the user is not allowed to do so:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql: FATAL:  permission denied for database "project2_core"
DETAIL:  User does not have CONNECT privilege.

This is what I expected. Now the strange stuff starts. I grant the user CONNECT:

postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           |
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
               |              |           |             |             | dietrich=c/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

And without any further grants, the user is allowed to create a table:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | adsf | table | dietrich
(1 row)

I would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGEon the schema and then GRANT SELECT on the tables.

Where is my mistake? What am I doing wrong? How can I achieve what I want (that a new user is not allowed to do anything before explicitly granting her the appropriate rights.

I'm lost, and your help is greatly appreciated :)

EDIT Following the advice by @daniel-verite, I now revok all immediately after creating the database. The user dietrich is not allowed to create a table any more. Good. BUT: Now, also the owner of the database, project2, is not allowed to create a table. Even after issuing GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2 and GRANT ALL PRIVILEGES ON SCHEMA public TO project2, I get an error ERROR:  no schema has been selected to create in, and when I specifically try to CREATE TABLE public.WHATEVER ();, I get ERROR:  permission denied for schema public. What am I doing wrong?


texts:
I'm designing a schema to support logging of user activity, where users must be able to search:

Across all events (events of any type), with datetime range, by username;
Across events of one type, with same as above and additionally with parameters of that module.

I created this schema:

And designed queries to search:

across all events:

SELECT extract(epoch from log_time) * 1000,
  u.username,
  CASE WHEN l.event_type IN (0, 2) THEN e.template
    WHEN l.event_type = 1 THEN format(e.template, ecs.field)
    WHEN l.event_type = 3 THEN format(e.template, ess.field)
    WHEN l.event_type = 4 THEN format(e.template, ect.ip, ect.port)
  END
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
LEFT JOIN event_client_search ecs ON ecs.log_id = l.id
LEFT JOIN event_switch_search ess ON ess.log_id = l.id
LEFT JOIN event_cable_test ect ON ect.log_id = l.id

across same-type events:

SELECT extract(epoch from log_time) * 1000,
    u.username,
    format(e.template, ect.ip, ect.port)
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
JOIN event_cable_test ect ON ect.log_id = l.id

This is what the event_def table looks like:

 id |                template
----+----------------------------------------
  0 | Logged in
  2 | Logged out
  1 | Searched in clients %s
  3 | Searched in switches %s
  4 | Cable test switch %s port %s

But what I didn't like, when I have about 100 events (now I have about 50, but haven't implemented them yet), it is going to be a performance issue.

So I thought to merge events with same parameters, like this:

I don't think that will help a lot (it'll cut the number of events by half at most), maybe I'm thinking in the wrong direction?


texts: What about redundancy if distributor goes down?

texts:
I'm setting up load balancing, transactional replication and mirroring. However, what I actually thought might work from the beginning, making the system redudant doesn't actually work because a Publisher can't have multiple distributors.

Preferable:

This is what was intended to do from the beginning:

Option2:

This is what we have to go with and my concern is that the weak spot is if remote Distributor server goes down it will stop syncing out the latest data. How can I make the distributor redundant?


texts: assistance with mongodb setup on windows server

texts:
I am currently setting up Mongo DB on a dedicated web server. I am not experienced with Mongo so I have some questions around its setup. I have done research in to Mongo etc but would like some assistance/direction on the following:

on the windows server this will be running on I will have an anti virus running on the server. Are there any files on mongo that I should avoid scanning?
in the area of clustering, what is the default/recommended setup for this? In what manner should it be setup in an enviornment it is storing data for a front wcf application.


texts: Postgresql update table within trigger: duplicate key value violates unique constraint

texts:
For a given table, I want to have a copy of all previous records in my database. I only insert data in the table.

I have an additional column seq to know which is the active record.

So, I use seq = 0 for the current record. Every record with seq < 0 is an old one.

This is the table (I ommit some columns to make it simpler)

CREATE TABLE public.machine (
    id character varying(25) NOT NULL,
    seq integer NOT NULL,
    vendor character varying(50),
    model character varying(50),
    CONSTRAINT pk_machine PRIMARY KEY (id, seq) );

Let me show some records:

"cat-1" 0   "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -1  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -2  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -3  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -4  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -5  "Caterpillar"   "D3K2 TIER 4 FINAL"

If I need to insert a new record, I do:

UPDATE machine SET seq = seq-1 where id = 'cat-1';

and then:

insert into machine (id, seq, vendor, model) values ( 'cat-1', 0, 'Caterpillar', 'D3K2 TIER 4 FINAL');

But his does not work. Some times, the update fails with the error:

ERROR:  duplicate key value violates unique constraint "pk_machine"
DETAIL:  Key (id, seq)=(cat-2, 0) already exists.

If the update changes row by row, each of a time, then it might break. If the update is made in the right order (starting from the minimum number), the update would work.

How do I do the update order by seq descending?

I wrote a trigger to to this, but it fails some times.

CREATE TRIGGER make_history
    BEFORE INSERT
    ON public.machine
    FOR EACH ROW
    EXECUTE PROCEDURE public.history();

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


texts: How to import the data of postgres 8.x to postgres 9.x

texts:
My OS is windows 7 x64. I was use postgres 8.4, and all databases are in postgres/8.4/data. Yesterday, I reinstalled my os system, but I found I can't install postgres 8.4 anymore (there is always an error when installing), so I installed postgres 9.1.

But I don't know how to reuse the data of postgres 8.x. I tried to copy the data from postgres/8.4/data to postgres/9.1/data, but the postgres service won't start.

Is there any way to reuse that. Since I have a lot of data in postgres/8.4/data, I can't loose them.


texts: Display table names in titles in SQL Server Management Studio

texts:
Does anyone know how to get SQL Server Management Studio to display the Table names in the Title.  Right now they are next to useless and every time I need to look at a table I have to reopen it.

In the good old days I used to use to Enterprise Manager and that was much more useful.

I found Brent Ozars article here, and the corresponding entry in Options... doesn't seem to be anything useful in there.  Maybe a 3rd Party Product?


texts:
I am running mysqld on Mac OSX Al Capitan. After boot up, I ran mysql.server start to start mysqld server.

But when I ran mysql.server stop, or sudo mysql.server stop, it just stopped for a while, later it will automatically restart itself again.

I also tried mysqladmin -uroot -p -hlocalhost shutdown for no avail.

It just keeps restarting again and again.

I guess it's maybe related to mysql_safe process, but I don't know how to properly stop mysqld, and force it not to restart again, until I myself run mysql.server start manually. Thanks!


texts: I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When operation is over, SQL Server is still holding the memory. How to free this memory reservation?


texts: Can I query data_space names when not in context of the database for multiple databases?

texts:
I have a data set pulled from sys.master_files.  The data set looks something like this:

database_id filename         physical_name data_space_id
----------- ---------------- ------------- -------------
6           PRIMARY_06       some location 1
6           primary_09       some location 1
21          PRIMARY_01       some location 1
26          PRIMARY_01       some location 1
52          PRIMARY_01       some location 1
75          PRIMARY_01       some location 1

I would like to join in the FILEGROUP name to this dataset.  It should just be a matter of joining on database_id and  data_space_id, but I can't seem to find a function or system view analogous to sys.master_files for filegroups/dataspaces.

The brute force method would be to create this list as a temporary lookup using sp_MSForEachDB, but I would like to know if there's a better way.


texts:
When I am running SQL profiler to trace the sql queries being executed, the trace is showing only Audit logs, not the sql commands being executed.
Can somebody tell me how to get the sql commands also?
Thanks


texts:
I'm designing a new system and I'm wondering what kind of DB should I use for my needs.

I have a tree structure of nodes, where each node has historic values. For each historic value it should hold 2 values:

The node's own value at that historic moment
The sum of all values of his children

Aside from that, each node could be defined as part of some categories - so I should be able to query the DB to get all the nodes of some given category (Example: Give me all nodes, under category CAT1, at a specific point in time). Each node could be part of several categories.

Further more, the historic data could have multiple versions at each data point - so if I have versions A,B,C - For each data point, I should be able to get the data for version A, B or C.

This of course can be achieved using a traditional SQL database (which I kind of created using PostgreSQL) - but that would require recursive queries to build each point in time (to compute values of all children). Also, though this data is relational - it feels that it can be defined better using non-relational tools. Just a feeling :)

Another thought was using NoSQL (MongoDB), where each node would contain its children - but then you have trouble getting to specific inner nodes without traversing all its parents and trouble using effectively the references of containing categories. I might be wrong about those points, though.

I would love to hear some pointers to what might be a good solution.

Thanks!


texts: Help needed designing a 'Group' concept for students in a class

texts:
I've been wracking my brain for a while now trying to figure out how to fit this part of my domain model into the database, but whatever I come up with just feels wrong.

I've attached a picture below of my current schema (it still needs a bit of work, but I'm focused only on this one issue right now).

The idea I need to model is as follows:

One or more students can potentially team up to form a group for the purposes of completing and submitting homework assignments.
A "group" is only valid for a given assignment. For subsequent assignments, a new group would need to be constructed. It may still consist of the same people as before, but this new group will receive a new identifier in the system.
Grades will be assigned to groups as a whole, so every student in the group will be getting the same grade.

One of my original ideas was to have a "Groups" table that would hold some metadata about the group, but there's no metadata that I can think of that would be meaningful, other than knowing what students are in the group, and for what assignment they are forming the group. This ultimately led me to having a join table with "User ID" and "Assignment ID" as foreign keys, while "Group ID" is a value that allows me to indicate that anywhere between 1 and n rows are in the same group. This column would necessarily be not unique, and therefore cannot form a primary key.

Is there a better solution for this than what I managed to come up with? Everything about this current design feels wrong, and I can't quite figure out why.


texts:
I am working in a SQL Server 2014 environment where there are multiple databases where the table structure (column names, data types, sizes) matches.  However, the indexes do not necessarily match.  For instance one database might have a clustered index on table X, but the next database does not, there may be clustered indexes in one and not in the other, non-clustered indexes may exist in one place where all columns are part of the index key columns while other cases have a mix with included columns, etc.

What I am in need of is a script which will allow me to level set indexes, without removing indexes which exist on the "target" database but not in the "template", while minimizing duplicate and overlapping indexes.  In lieu of such a script, some suggestions or guidance in creating one would be a viable option.

Thanks!


texts:
I have a report parameter which depends on a query from the Dataset. The query needs one parameter and sql server reporting has no parameter drop down

version - Microsoft SQL Server 2016 Report Builder


texts: How does MySQL/InnoDB caching work internally when you perform two similar, but not identical, select requests?

texts:
I have some confusion about how the Innodb buffer pool works.

Let's say I have table with 15 records, if I execute: select ... between 1 to 10 then that result will cached in buffer(?).
My question is: if i execute select .... between 1 to 15 next time, then how it will fetch the records. Is it select 10 records from the buffer and 5 records from disk?


texts: Is there any tangible difference between a unique clustered index and a clustered primary key?

texts: I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?


texts: MongoDB query: date range (but dates in collection are in NumberLong format)

texts:
I need some help with my MongoDB query.
I was trying to count in collection the number of entries for a specific date (i.e. May 05, 2016), but dates in the collection are in NumberLong data type. So I did this query:

db.workerLog.find({$and:[{"startTime" : { $gte: NumberLong(1462377600000)}},
    {"completeTime" : { $lte: NumberLong(1462463999000)}}]}).count()

However, upon checking the results, the results set contain entries after May 05, 2016 (like May 06, 2016), such as this:

{
  "_id" : ObjectId("572c4cb5c497211b8ed9a91e"),
  "jobId" : "572c4cb4c497211b8ed9a91a",
  "resourceType" : "CHART",
  "resourceId" : "57281f1e45e91408a197a07f",
  "startTime" : NumberLong(1462521012014), -- this is Fri May 06 2016 15:50:12 GMT+0800 (Taipei Standard Time)
  "completeTime" : NumberLong(0),
  "source" : "itunes",
  "status" : "OK"
}

Can anyone help?


texts:
Consider the following exam question:

Translation: The INCORRECT transformation from the diagram at the right to tables is...

This is what I think:

a) is incorrect (the column es_C (is_C) does not make sense to me (is always true)

b) is correct, I don't see anything wrong with it.

c) is incorrect, you cannot represent C which are not S1 nor S2 (and the diagram doesn't say the hierarchy is total)

d) is incorrect. The column es_C_S1_S2 (is_C_S1_S2) does not make sense to me.

There is only one incorrect answer so, can you show me what is wrong with my thinking?

The circled 'd' means disjoint hierarchy, that is, there cannot be a S1 entity which is also a S2 entity.


texts: What is the optimal solution for converting a database schema?

texts:
I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint names plus we're doing a bunch of cleanup and renaming of fields, tables etc.

I suppose a better way to frame the question is that given an existing database that needs to go through the following:

Create all brand new foreign key constraints with names that work within the framework
Tables will be renamed and/or massively restructured including renaming fields that are currently part of existing foreign key relationships

What is the best way to accomplish this?

My solutions thus far have been:

Create a conversion script (in php) that will run DROP FOREIGN KEY for all of the constraints in the database. This has resulted in weird errors occurring during the later steps of the process when trying to ALTER TABLES and add in the new constraints. Also, the constraints were removed but I get errors if I try to DROP the keys themselves.
Instead of using PHP and PDO, write the conversion script using straight SQL and use a CURSOR to DROP the constraints.
Instead of dropping the constraints themselves, convert the tables to MyIsam and DROP the keys then convert back to Innodb before continuing with the rest of the conversion process.
Create a brand new schema from scratch and then move the data from the old schema to the new.

I would like to know which of these approaches would be considered the most valid? Or if there is a better way of doing this?

UPDATE (this information was requested in the chat room so I thought I would add it here):

The current schema has 68 tables, the new schema will be closer to 55.
The largest table has just over 800,000 rows and in general our tables are in the tens of thousands.
The entire database is roughly 240 MB but most of that is in a single table that stores a large text field. We'll be compressing this during the conversion.


texts:
I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE table ADD FULLTEXT INDEX index (column ASC) to create a fulltext index returns an error, saying I cannot create a fulltext index on that column.

When I go back and look at the specs for the table, it appears as BLOB, instead of text.

I'm using MySQLWorkbench, but I've generally been running the scripts by hand, instead of using the wizard.

I have tried setting a character encoding for the entire schema (none existed before) thinking that this would be needed for text columns, but it didn't help.


texts: SQL Server : keep track of a running calculation monthly

texts:
I have the following in Excel which I need to calculate in SQL Server:

The columns that needs to be calculated is change and new_threshold:

The formula for change is:

While the formula for new_threshold is:

I have tried to use the lead and lag methods SQL Server offers, but I can't seem to keep track of the calculation.

Here is one thing I've tried:

SELECT
    year_month,
    round(original_threshold, 0) original_threshold,
    round(case when original_threshold > isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end, 0) change,
    manualChange,
    round(original_threshold + case when original_threshold > isnull(lead(original_threshold) over (order by [program], [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end + manualChange, 0) new_threshold
FROM
    [Test].[dbo].[test]

which yields:

Edit:

Here is the insert statement if you want to test it

insert into [Test].[dbo].[test]
(
     [year_month]
    ,[original_threshold]
    ,[manualChange]
)

values
('2017_09', 401,    0),
('2017_08', 396,    0),
('2017_07', 397,   -5),
('2017_06', 389,   -7),
('2017_05', 393,    0),
('2017_04', 390,    0),
('2017_03', 412,    0),
('2017_02', 439,    0),
('2017_01', 458,    0)

Based on the values from this insert, I want to be able to calculate the change and new_threshold like in the Excel above.


texts: improving performance of concurrent inserts on innodb with randomized primary

texts:
I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator.  It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up generating surrogate keys in monotonic increasing order.

Therefore I am worried with inserts from lots of different users to the same table for that primary surrogate key being used, since the inserts will all be using numbers near each other at a given point in time, might there be enough gap lock contention to slow down the insert?

I have two workarounds in mind.  One is to use an md5 hash of the BIGINT number as the actual primary key.  This way, all concurrent inserts are likely to lock different gaps.  The other solution is that even though for programming simplicity, it would be best to use the the new BIGINT given by my sequence generator as the surrogate id, instead use a primary key tuple (user_id, surrogate_int_id).

Also, if there secondary indices on the table do inserts take gap locks on all of these as well?


texts:
Is it possible to do something similar to the following in SQL Server 2012?

IF EXISTS (
    WITH DATA AS (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
        FROM table )
    SELECT *
    FROM DATA
    WHERE rn = 2 )
BEGIN
...
END

I tried using this syntax and received an error. If this is not possible, would using a temp table be the best way to accomplish this?


texts:
On MariaDB 5.5, Ubuntu 14.04

What actually happens when I restore a 170mb mysql dump file and info "Query exceeding 16384 bytes, skipping. Probably a bulk insert query." shows on cmon.log?

Does it mean that I would not have all my backup data restored?

Below is output of max_all settings

> show variables like '%max_all%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 536870912  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)


texts:
Are the SQL Server service packs 'cumulative' or 'incremental'?

The scenario is that we have a couple of instances running on SQL Server 2008 R2 RTM and we have been asked to patch it up to SP3 level. I'm not sure if I just apply SP3 or do I need to do SP1 - SP2 - SP3


texts:
I have a table storing information about tickets. There is  one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' to mark which one it is. There are other columns in the tables and some of them contains values when it is sales but not when it is used and vice versa. The table is actually a fact-table in a data warehouse style.

Amoung other things I am calculating time difference between sales and used so I am joining the table to itself in order to get one record for each ticket to be able to have timestamps for both event in the same record to calculate.

I need to include all sold tickets so an outer join should solve that.

First I ran this query

select x.*
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and y.TransType = 'used'

When I run it the filter x.TransType = 'sold' is not working and the query return results for all records in factI, regardless of TransType. If I use inner join this works but obviously not returning tickets which haven't been used.

so I changed the query, to this one which gives me correct results.

select * from (
   select * from factI where TransType = 'sold'
) as x
left join (
   select * from factI where TransType = 'used'
) as y on x.ticketId = y.ticketId

Why is it that the where clause in the first query isn't filtering out correctly when I use an outer (left) join?


texts: SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?

texts:
Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form creation tool.  Each form type created using the tool is represented in the database by a table with a primary key of datatype VARCHAR(50).

What impact would the requirement to use VARCHAR(50) primary keys have on system performance vs. a system using a more conventional design approach, e.g., this?  Assume the core audience for the answer is mostly non-technical, i.e., C-Level Executives making a go or no-go decision on the solution.

Notes:

The document control management system needs to perform typical document control management system tasks for a global mid-sized company for many years (at least two decades).
This is a canned solution and the primary key data type cannot be altered.
The VARCHAR(50) data type maps to the company's serial number for products.  The serial number format is alphanumeric and can contain dashes.
The system is web based and uses SQL Server 2014 and the .NET Framework (C#) technology stack.
Questions researched before creating this question, all of which don't align with the technology stack and/or have answers too in the weeds for a non-technical audience:

VARCHAR primary key - MySQL
Should I replace my varchar primary key with an integer primary key?
Can I use VARCHAR as the PRIMARY KEY?
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?


texts:
Here's the scenario:

create table a (
 id serial primary key,
 val text
);

create table b (
 id serial primary key,
 a_id integer references a(id)
);

create rule a_inserted as on insert to a do also insert into b (a_id) values (new.id);

I'm trying to create a record in b referencing to a on insertion to a table, but what I get is that new.id is null, as it's automatically generated from sequence. I also tried it with trigger, that is launched AFTER insert FOR EACH ROW on a table, but result was the same. Any way to work this out?


texts: can i use mysql views to update and delete the records in mysql

texts:
I am able to create a view for retrieving the records based on some criteria, but does the view only support for retrieving the records or will it support for delete and update the records in the tables.

create view onlinetestview as
    select * from onlinetest where experience = '1-2 Years'

I used the above view its working fine but when i am trying to create a view for updating the table record its giving me an error:

create view onlinetestview2 as
    update  onlinetest set experience='1-2Years' where experience='1-2 Years';

The error is : Lookup Error - MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update  onlinetest set experience='1-2Years' where experience='1-2 Years'' at line 1


texts: SSRS output to file subscription not working

texts:
I'm new to Reporting Services.  We have SSRS 2017 set up on a Windows Server 2019 Standard box.

I've got my report all written and tested but given the size of the output, I'm trying the subscription option to write the output to a local file, but so far I've run into nothing but problems.

I'm trying to write to \\localhost\ReportOutput as a share.  I've granted full control on that folder to all the accounts I've tried to use.

First problem - whenever I've tried the * Use The Following Windows user Credentials option, I can type in the creds, but when I click Apply, it blanks out the password.  When I re-edit the subscription I get warning/errors that those fields can't be blank.  The username is not blank (though it gets the warning anyway); the password is blank.

Second problem - no matter what creds I enter, I get "The permissions granted to user '' are insufficient for performing this operation." as an error when the report is run.  '' is not trying to hide my creds - that's really what the error says.

Third problem - I've tried configuring the system wide Subscription Settings File Share Account and configuring the subscription to use that instead.  When I run the report, I get the same "permissions granted" error above.  I've tried using both domain and local users in this setting.

When I look at the log file, I see these error messages consistently before the insufficient privs error:

Reporting Services account may not possess permission to perform authentication checks.
Check the Windows Authz documentation for more information and details on diagnosing issues.

I've done a lot of googling trying to find what to do about it, but most of the results associate it with email delivery failures (which we are not having), and some of the suggestions (like granting a user 100% read privs in the domain controller) are things I don't have the ability to use.

Has anyone else had so much trouble with SSRS outputting results to a file?

Thanks


texts:
The information model I am modelling from includes an "any type". For example a customer can have zero or more 'entries' each of which can have a number of values. Each value could be a either date, numeric, string or spatial. Note query performance is more important than insert performance.

I wondering if you have any ideas for table design.

My ideas are:
  (a) a single table encompassing all the types. Each record in entry_data would only use one column for the value. Also each valuetype column needs to be indexed. This seems a little wasteful (although I'm aware oracle won't index the null values )

create table entry (
  id number(12) primary key,
  name varchar2(256) not null unique,
  datatype varchar2(256) not null,
  customer_id not null references customer(id)
);

create table entry_data (
  id number(12) primary key,
  entryid number(12) not null references entry(id),
  strvalue varchar2(256),   -- index
  datevalue date,   -- index
  numvalue number,   -- index
  sdo_geometry geom  -- index
);

OR (b)  create a separate entry_data table for each type

create table entry (
  id number(12) primary key,
  name varchar2(256) not null unique,
  datatype varchar2(256) not null,
  customer_id not null references customer(id)
);

create table entry_data_str (
  id number(12) primary key,
  entryid number(12) not null references entry(id),
  strvalue varchar2(256),   -- index
);

-- etc

but I'm not sure if introducing more joins will impact the performance.

Any suggestions welcome.
Thanks.


texts: Many to Many Table with multiple same-column references

texts:
I'm designing a document translation DB and struggling to connect the non-generic many-to-many table.

Each document has a title and summary.

Translated Document table:

CREATE table translated_documents (
  document_id         bigint references documents (id),
  source_language_id  int not null,
  source_title_hash   uuid, -- md5 hash cast to uuid
  source_summary_hash uuid,
  PRIMARY KEY (document_id)
);

CREATE TABLE translations (
  content_hash            uuid not null, -- holds either source_title_hash or source_summary_hash
  translation_language_id int  not null,
  content_translation     text not null,
  PRIMARY KEY (content_hash, translation_language_id)
);

An attempt at a many-to-many table, trying to reference the content_hash column twice.

CREATE TABLE translated_documents_translations (
  document_id         bigint references translated_documents (document_id),
  source_title_hash   uuid not null references translations (content_hash),
  source_summary_hash uuid not null references translations (content_hash),
  PRIMARY KEY (source_title_hash),
  CONSTRAINT same_hash_not_allowed CHECK (source_title_hash <> source_summary_hash)
);

My thinking is that documents can be translated into many languages, and translations can work for many documents with the same title or summary. Is there a better way to set this up?


texts: Is It Possible to Run Out of Log Sequence Numbers?

texts:
I’ve been wondering about the algorithm for generating log sequence
numbers for transaction log records, and I’m concerned that with a
high enough workload it might be possible to run out of log sequence
numbers.

What happens in that case?


texts: SQL Server: Cannot resolve collation conflict between "Latin1_General_CI_AS" and "[garbage]"

texts:
So I'm writing a large UDF in T-SQL to be used in reporting. The UDF contains quite a few common table expressions.

At one point I was adding another CTE:

 cteCmtCauses AS (
    SELECT ProductId = p.Id,
           Name = hz.Name,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]

    UNION ALL

    SELECT ProductId = p.Id,
           Name = c.Name,
           --C = c.Cmr_HasCarcinogenicRisk,
           --M = c.Cmr_HasMutagenicRisk,
           --R = c.Cmr_HasToxicForReproductionRisk,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]
 ),

 cteCmtCausesConcat AS (
    SELECT ProductId = p.Id,
           ComponentIds = (
                -- Here the issue happens
                SELECT CONCAT(cte.CMT, N'|', cte.Name, dbo.QueryConcatenationString())
                FROM cteCmtCauses cte
                WHERE cte.ProductId = p.Id
                FOR XML PATH(N''), TYPE
           )
    FROM [redacted]
 ),

When trying to persist the UDF mutation, I got this error:

Msg 468, Level 16, State 9, Procedure QueryProduct, Line 93
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "널㾍.鉀杫.....祉߾.䊙꛸.鈀杫..." in the concat operation.

And in fact on every attempt the message changed a little bit:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "࿠䚋.剀焩.....祉߾.䊙꛸.刀焩..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠䥆.뉀洶.....祉߾.䊙꛸.눀洶..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠洦.퉀洷.....祉߾.䊙꛸.툀洷..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "焐柘.牀䯏.....祉߾.䊙꛸.爀䯏..." in the concat operation.

I was able to work around it by using:

SELECT CONCAT(cte.CMT, N'|', cte.Name COLLATE Latin1_General_CI_AS, dbo.QueryConcatenationString())

But the weird thing is, everything in the database and tempdb has the same collation: database collation, being Latin1_General_CI_AS (except from one of the tables used in the UNION ALL which is Latin1_General_CS_AS).

Gist of full reproducible sample, make sure that the database collation is Latin1_General_CI_AS.

How do I properly resolve this issue, is this a known bug, and do I need to worry about SQL server silently corrupting my data once I start using this UDF?

Using

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Sep 23 2016 15:49:43
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


texts:
I have a table structure that holds historical data for another table. Something like

id      | name        | date
----------------------------------------
1       | John        | 2016-01-01 14:38.123123
1       | John Smith  | 2016-01-03 16:46.123123
2       | ...         | 2016-01-01 14:38.123123
3       | ...         | 2016-01-01 14:38.123123

I want to make a query that gets each row, and for each row, also get the previous row (latest date before the rows date) with the same id, e.g. something like

SELECT id, name, date, previous.id, previous.name, previous.date
FROM ..?

Is this possible? This is a pretty general question, but in my specific case, I am using PostgreSQL, so if there is some postgres features that accomplishes this, that is fine.


texts: Does plan recompilation happens to plan guide?

texts:
Does plan recompilation happens for plan guide due to the, for example, auto update stats?

P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide


texts:
I want to log information using a 2016 SSIS C# (2015) script task that will be easily findable in my SSIS reports I have built. My plan is to use the IDTSComponentEvents.FireInformation method, and use the subcomponent parameter to surface the identifier that I will filter on to only pull these messages. I am able to fire events from my script just fine, but I cannot seem to populate the subcomponent_name column in SSISDB.[catalog].[event_messages]
I can demonstrate with a simple package with a single script task in the control flow. My main method has the following code
public void Main()
    {
        bool fireAgain = true;
        Dts.Events.FireInformation(1, "eventName", "This is a message", "null", 0, ref fireAgain);
        Dts.TaskResult = (int)ScriptResults.Success;        }

If I run this in SSDT I can see my Subcomponent name and my log message:

I deploy to SSISDB (I have tried this with our 2016 server, and a 2014 instance) and execute with a Basic log level, and I do not see the subcomponent_name in the standard report, or in ssisdb.[catalog].[event_messages]

I can filter on the message_source_name for my needs right now easily enough, but it seems odd that this parameter for this method surfaces the sub component name in the SSDT execution results, but not in the SSISDB message log.
How do you populate the Subcomponent Name in an SSISDB message using an SSIS Script task?


texts:
I have SQL Server job. In the last step of the job, I want to stop/start SQL Server.

Next, I created .bat file with NET STOP "MsSqlServer"

I tried SSIS package that uses the execute process task with cmd.exe and then execute the .bat file to restart mssqlserver and it works fine...

However, when I called this package in the SQL Server job, it stops the service but does not not start it back.

the question is how to stop/start sql server after another sql serve job complete..?

i have sql server/analysis services/reporting services in one station with 64 GB Memory.and i have nightly job with this steps :

1-ETL that Update DW
2- Process Analysis services Database
3-Update Reporting services Subsciptions

Sometimes, in beginning the AS Processsing in the step 2, it gives me error: there is no resource Available and and the job got failed.

All I want is a good memory configuration for this environment.

what strategy I must choose for Memory Management ?

Another problem: After step 2 for update subscription (i have about 50 Subscriptions), if I try Update Subscription with clean memory (after I restart SQL Server service), the subscription executes very fast.

If i don't restart the SQL Services, it take long time to run and sometimes it never gives me results.


texts: How to increase max_connections in Postgresql-9.2

texts:
I need to increase production Postgresql-9.2 max_connections parameter from current 1000 to 3000.
Used PGTune service to get the values and updated accordingly to the postgresql.conf file and /etc/sysctl.conf.
/var/lib/pgsql/data/postgresql.conf
# DB Version: 9.2
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 16
# Connections num: 3000
# Data Storage: ssd

max_connections = 3000
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2796kB
checkpoint_segments = 3

/etc/sysctl.conf
kernel.shmmax=17179869184
kernel.shmall=4194304

After this modification, Postgresql service failed to start with semaphore error.
As I dont have any idea on how to update this value with respect to OS/HW spec, updated with some value set from internet (kernel.sem = 250 32111 32 128). Still Postgresql service failing to start with following error messages,

FATAL:  could not create semaphores: No space left on device DETAIL:
Failed system call was semget(5432129, 17, 03600). HINT:  This error
does not mean that you have run out of disk space.  It occurs when
either the system limit for the maximum number of semaphore sets
(SEMMNI), or the system wide maximum number of semaphores (SEMMNS),
would be exceeded.  You need to raise the respective kernel parameter.
Alternatively, reduce PostgreSQL's consumption of semaphores by
reducing its max_connections parameter.

Need help on setting semaphore values in order to enable 3000 concurrent connections to the DB.
What are the parameters to be considered for this?
System spec

OS: Centos-7.4
CPU: 16 core
RAM: 32GB
Volume: 450GB SSD
Swap: 16GB SSD
No. of DBs: 3
Total DB base size: 290GB


texts: Can SQL Server Bulk Copy cause timeouts when querying the table

texts:
In SQL Server 2008 R2, can using BulkCopy to upload thousands of rows of data cause problems for queries taking place on the same table?

Specifically could it cause the queries to time out?

The table being queried has millions of rows.


texts: Modelling an entity-relationship diagram for a scenario involving companies, services, and clients

texts:
I am creating an entity-relationship diagram (ERD) for managing workflow and other details of Company X in which I am maintaining details of the following objects:

Employees of Company X
Services offered by Company X
Employees of Company X Associated with the services
Company X's Employee Multiple Mobile Numbers
Employee Multiple Addresses
Employee Multiple Email ID (One Default)
Account of Employee
Request for Service from Client
Client Information

I have created the following ERD until now.

Entitiy types and their Purpose:

Person entity type to store details of the person
Employee entity type to store details of employee.
Service entity type to store details of services
Service Associated entity type to store details of which Employee is associated with which Service
Mobile Number entity type to store any Mobile Number
Address entity type to store the Address of any Person
Email ID entity type to store Email Id of any Person
Service Request to store details of Service requested

Now I want to store the details of the Client.

A client could be of two type:

Individual
Company

If the Client is an Individual, any Person can request for a Service.

If the Client is a Company, a Person will request on behalf of the Company say this Company Employee.

So, we want to store information of Individual Client i.e. Name, PAN Number, Mobile Number, Email ID, Request Details,

And other information is Details about company i.e. Company Name, PAN Number, Address, Requests,

And Employee of this Company who is requesting on behalf of a Company i.e Name, Email ID, Mobile Numbers, Request Requested.

NOTE: I am working alone on this project and teachers are not helping me much. I am creating a database design for the first time by learning online from various resources. Your guidance will be helpful to me. Thank You.

Does anyone could clear me where that information will store.

Should I make a new entity type for storing Company Y employee details or should I add them in person entity type and connect it with the client company entity type?

And how should I store individual client information?

Is there any mistake in the ERD?


texts: How does Database Snapshots Work (MySQL)?

texts:
I have a MySQL database hosted on an AWS RDS instance. The database has many tables and I observed the following:

I picked a table with about 1-2 million records that is indexed on a created_at datetime column. When I execute a SQL query something along the lines of picking records between a specific start date and end date on this column, the query takes a long time to complete(about 10 mins).

However, when I take/restore an AWS RDS instance from a snapshot, it hardly takes 5-10 mins. I'm curious how taking/restoring from snapshots are so fast? Does is not have to read through the whole data in the database to take/restore snapshots?

Forgive me if my question is naive. I'm a newbie. Thanks for the input :)


texts: Understanding MS SQL Server encryption and backups

texts:
So I was asking in chat about how secure SQL Server backup files are.

I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data.

So lets look at this scenario :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

notice - there is no password here.

and then we encrypt our tables by :

UPDATE tbl1
 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)
GO

Now lets say a hacker got my bak file. All he has to do in order to view the data (in his own computer & sql-server) is:

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM tbl1

Would he still be able to access the data?


texts:
I am facing database error Incorrect string value: '\xC5\xABt\xC4\x97'. I did a lot search and tries a few ways, include move from utf8 to utf8mb4. But still not able to solve it.

Following are the database chartset, collation, and table schema.

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

| DELIVEREDEMAIL | CREATE TABLE `DELIVEREDEMAIL` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RelatedModule` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserUUID` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Username` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmailType` enum('NEW_PRECITION_EMAIL','ACTIVITY_EMAIL','LOG_IN_BASED_EMAIL','REPLAY_TO_PREDICT_EMAIL','NEW_TVSHOW_EMAIL','NEW_REWARD_EMAIL','TUNE_IN_EMAIL','EXPIRED_PREDICTION_EMAIL','NEW_FOLLOWER_EMAIL','UCP_UCT_CONFIRMATION_EMAIL') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmailSendingFrequency` enum('DAILY','WEEKLY','BIWEEKLY','MONTHLY','ASAP','UNSUBSCRIBE','SUBSCRIBE') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SendTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `DataInEmail` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Code` int(11) NOT NULL,
  `Message` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `notification_senttime` (`SendTime`),
  KEY `notification_emailtype` (`EmailType`),
  KEY `notification_useruuid` (`UserUUID`)
) ENGINE=InnoDB AUTO_INCREMENT=3807922 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 |

The error I got is when try to store username such as "Тхьабысым", I got "Incorrect string value: '\xC5\xABt\xC4\x97' for column 'p_Username' at row 4".

Anyone have some idea? Thanks in advance!

EDIT:
I tried to change charset to latin_1 and collation to latin1_swedish_ci, and it worked. Does it means "Тхьабысым" are latin characters but 4 byte UTF-8 as utf8mb4 still not able to represent these characters? What should I do with these kind of characters? Since this is username pulled from Facebook, there can be all kinds of characters.


texts: How to include a where clause in a query from another column

texts:
I have two tables:
pointer_table(col_name varchar, col_val varchar, wh_clause varchar)
value_table(col_1 varchar, col_2 varchar, value varchar)

col_name in pointer_table denotes the column in value_table (either col_1 or col_2), while wh_clause contains a complex where clause to be used in the value_table.

When working with col_name and col_val, it's easy to join the two tables eg:

select vt.value
from (select col_val from pointer_table where col_name = 'col_1') pt
inner join (select col_1, value from value_table) vt
  on pt.col_val = vt.col_1

However I sometimes need to apply the contents of pointer_table.wh_clause on value_table (wh_clause contains conditions like col_1 = 'pine' and col_2 = 'Argentina'.

Is it possible in DB2 to somehow apply the content of that field as where clause for another table in a query?


texts: How to create a trigger that update the date field and time field upon modify of the row?

texts:
I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

However, how do I write a trigger such that it will update that particular row that has been modified (or updated with new information) and record the modification date into the expire_date field and the modification time into the expire_time field? (or if this is possible?)


texts: DB Design: Looking for Performance improvement with a Column used in all SQL Queries

texts:
I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running stored procedures.

The database designed is highly normalized. In all the tables we have a BIT column as [Status_ID]. In every Stored procedures, For every tsql query, this column is involved in WHERE condition for all tables.

Example:

Select      A.Col1,
            C.Info
From        dbo.table1 A
Join        dbo.table2 B On A.id = B.id
Left Join   dbo.table21 C On C.map = B.Map
Where       A.[Status_ID] = 1
And         B.[Status_ID] = 1
And         C.[Status_ID] = 1
And         A.link > 50

In the above sql, 3 tables are involved, [Status_ID] column from all 3 tables are involved in the WHERE condition. This is just an example. Like this [Status_ID] is involved in almost all the queries.

When I see the execution plan of most of the SPs, there are lot of Key lookup (Clustered) task involved and most of them are looking for [Status_ID] in the respective table.

In the Application, I found that, it is not possible to avoid these column checking from queries. So

Will it be a good idea to

Alter all [Status_ID] columns to NOT NULL, and then adding them to PRIMARY KEY of that table.
Adding [Status_ID] column to all the NON Clustered indexes in the INCLUDE PART for that table.

Please share you suggestions over the above two points as well as any other.

Thanks for reading,


texts:
Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {"1": 0, "2": 3, "3": 15, "4": 15, "5": 0}. The 15 would be like a default value I pull from another column.
Another acceptable output would be an array with the values '{0, 3, 15, 15, 0}'.
Sounds too complex for pure SQL? If not possible, help with a PL/pgSQL would also be appreciated.


texts: Difference Between Row Level and Page Level Locking and Consequences

texts:
When attempting to run my Maintenance Plan, I receive the following error:

  Executing the query "" failed with the following error: "The
  index "" (partition 1) on table "" cannot be reorganized
  because page level locking is disabled."

We currently have Row Level locking enabled on this Index. I can enable Page Level locking, but I am unsure what the repercussions are.

My Question Is: What is the difference between the two locking schemes, and what are their real-world (in production) consequences?


texts: Why is a SELECT grant needed for an UPDATE statement on a table? (Error 1143)

texts:
myuser@'%' has UPDATE and INSERT grants on table1.

I tried to run this UPDATE:

UPDATE table1 SET column1 = 'blahblah' WHERE id = 123;

I got this error:

  SELECT command denied to user 'myuser'@'localhost' for column 'id' in table 'table1'

... and error code 1143.

Of course, I understand that the particular UPDATE I was trying to run needs to pick just the particular row I want, probably using the equivalent of a SELECT statement "under the hood" so to speak, to find the row identified by id = 123, but it doesn't seem right that I should need the SELECT grant when I'm not doing an explicit SELECT.

Can someone help me understand the logic that calls for a SELECT grant for this very common sort of UPDATE?


texts:
I am trying to purchase a new Server to run MySQL Server on. This new server will be a slave of my main machine. However, this server will be dedicated for reporting only "Lots of reads and complex queries."

Now I am looking into investing into solid state hard drives but was wondering if it's really worth the price. The difference between an SSD and a SATA 7200 hard drive is about $1500 and the SSD has less disk space. If I do invest into SSD will the speed be noticeable?

I can purchase 4 (500GB SATA 7200) for $1500 less than purchasing 2 (500GB SSD)

Can you please help me making the decision to see if it is worth the upgrade or not?

Once more thing that I would like to mention is that I am not using query_cache so there will be lots of disk reads.

This server will have 32GB of RAM and will run Ubuntu 12.04


texts: Mysql upgrade to 5.7: Data too long for column

texts:
I just upgraded mysql from 5.6 to 5.7 .A query used to work but after upgrading it don't seem to work anymore..The below error when i run the query on the mysql 5.7

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'last_touch_by' at row 1

Can anybody help..
Thanks


texts: SQL Server AlwaysOn: Lost of heartbeat and connection with secondary replica

texts:
Virtual Enviroment description:

Hypervisor: VMWare

WSFC composed by two nodes with Windows server 2016 Standard

SQL Server AlwaysOn with synchronous replica and automatic failover

SQL Server 2014 (SP3-CU2) 12.0.6214.1

Following the cluster thresholds:
CrossSubnetDelay          : 4000
CrossSubnetThreshold      : 40
PlumbAllCrossSubnetRoutes : 0
SameSubnetDelay           : 2000
SameSubnetThreshold       : 20

AG properties:
LeaseTimeout: 20000
FailureConditionLevel: 3
HealthCheckTimeout: 30000
VerboseLogging: 0

Issue:
RHS.exe process on primary node lost heartbeat with availability group and it initiated the failover. Immediately after the lost of heartbeat, primary node loses the connection with the secondary node and automatic failover fail. Shortly after, hearbeat with primary node works again and primary node takes over the resources again.
Log Details
In chronological order:

Primary node randomly shows signs of disconnection with the secondary replica already in the days leading up to failover:
...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
The primary node storage randomly shows signs of distress already in the days leading up to failover and just before failover:
...Long Sync IO: ... IOs in nonpreemptive mode longer than 1000ms
FlushCache cleaned up 146070 bufs with 20273 writes in 80387ms...
SQL Server has encountered 4 occourence(s) of I/O request taking longer than 15 seconds to complete...
RHS.exe lost heartbeat with AG and WSFC send failover request to AG:
[hadrag] Failure detected, diagnostics heartbeat is lost
The local replica of availability group ... is preparing to transition to the resolving role..
Shortly after the failover request, the primary replica loses connection with the secondary:
...AlwaysOn Availability Groups connection with secondary database terminated for primary database 'nameofDB'..
The listener stops working and there is a checkpoint failure on a specific database:
One or more recovery units belonging to database .. failed to generate a checkpoint
Eleven seconds after the attempted failover, heartbeat works again and primary replica
takes over the resources

Questions
Million dollar questions:

why heartbeat was lost?
why connection with secondary replica was lost ?
Any workaround ? increase lease timeout, performance of storage or RAM and CPU can help?

I understand that it is difficult to answer but it can help to only have a strategy or hypotheses that can help me find the problem.
EDIT
I found an interesting example of extended event able to trace the duration of all synchronization steps on tigerteam github. The problem is that it can degrade performance and I haven't found a doc reference for analysing the values shows by extended events. Any idea or suggest for troubleshooting?


texts: I noticed that in the sql failover clustering under the possible owners none of the nodes has been checked, I have a two node cluster does that mean in the event of failover it will failover to any of the active nodes. Whats is the difference if both of the nodes are checked vs if both of the nodes are unchecked as possible owners.


texts: Backup is getting failed with following error

texts:
Msg 3202, Level 16, State 1, Line 1
  Write on "\w-mum-fp\SQLBKP\Weekly-FL\Interactions_26_01_2014.bak" failed: 1130 (Not enough server storage is available to process this command.)
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Available storage is 400 GB and database backup size with compression is 50GB, still backup is failing. Kindly assist.


texts: What happens when the mirroring server goes offline (for a short while)?

texts:
Let's say we have a principal server, a mirroring server (the one that serves as the failover backup) and a witness server.

Does it matter if I were to bring the mirroring server (not the principal) offline for a short while (five minutes, let's say). Is this going to break the mirror? Is there anything I need to do (such as turning the mirroring off temporarily on the mirrored databases) before I take the server offline?

There are plenty of resources online to explain what needs to happen when the principal goes down, but nothing I can find about this scenario.


texts: When is the time that Couchbase write documents to disk?

texts: As the title said, when is the time that Couchbase write documents to disk? every 60 seconds? every 1 seconds? every 100 ms? or everytime there are read command to that document?


texts: Find the partition where data lives in MySQL

texts:
Is there a way to select the partition name where a specified record actually lives in? The idea is something like:

SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition>

I found a similar question for SQL Server: Get a record's current partition, but not for MySQL.


texts: sqlldr running slow after indexes created

texts:
let me explain this....

I had running 1 session of sqlldr while I run indexes into table for 6 fields... now the indexes are completed but the sqlldr running very slow it load one file after each 30 minute...

now when I do any alter to set the indexes off it won't let me do it and though an error of ( ORA-000054 and ORA-000604) even if I run another sqlldr....

any suggestion will be highly appreciate.... bellow is sample of my control files header...

OPTIONS(DIRECT=TRUE,ROWS=10000,BINDSIZE=209700000,readsize=209700000)
load data
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols

When I run following query

select * from dba_dml_locks

session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others
123,username,tablename,exclusive,none,1228,not blocking

any suggestion ... this is my daily scheduler to load around 8000+ files around 20GB so any suggestion