texts: Anonymous and Public User Table Design in twitter like application

texts:
I have schema design problem which I am not 100% sure if my assumptions are correct.
So the requirements are:

I need to have 2 different type of user, anonymous and public.
A user can have both anonymous and public profile.
A public and anonymous user can follow anonymous and public.
Anonymous user and public user must not be identifiable by clients. Which means if I know public user I shouldn't be able to fetch anonymous profile details vice versa.

I first come up with idea:
User (id, anon_id,public_id)
AnonymousUser(id(PK,FK),nickname)
PublicUser(id(PK,FK),nickname,fullname,phone,is_phone_registered)
RelationShip(id,follower_id(FK-User),following_id(FK-User))
Tweet(id, author_id(FK-User),text)

In this design I could split users into two. However, if we know the tweet's author id we are not able to understand if it's send by public or anonymous account. And also the same problem exist in Relationship too, we are not able to understand if follower/following is anonymous or public. The solution might be adding 2 more fields to Relation clarify follower/following type ie. follower_type/following_type and 1 more field to Tweet author_type.
Another solution which I felt more correct is
User(id(PK),nickname,fullname,phone,is_phone_registered,public)

In this design I can have both anonymous and public profiles in same table. And records would look like:

id
nickname
fullname
phone
is_phone_registered
public

123
slayer
John Doe
+1 90 123 123 12 12
true
NULL

312
vendetta
NULL
NULL
false
123

And public field should not be visible to clients so I am pretty much achieving my goals on security side of things.
Sorry for my ignorance I am having NoSql background and in sql world I feel like I need achieve:

Single responsibility in tables
Get rid of a lot of redundant null's
Feels like self referencing column is an anti-pattern, which is public field is referencing to other rows id.

All suggestions are welcomed.


texts: What is the bare minimum access needed in the public role?

texts:
I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures.

However, by default the user gets everything in the "public" role. I took a look at the various permissions and it appears that public still gives them WAY too much access. However, I'm afraid if I revoke these permissions something unexpected will break.

Are there any default or open source scripts that lock public down safely? And if not, is there at least a list of "bare minimum" permissions I'd need to leave as-is? Additionally, is there any easier way to create the scripts than me manually walking through every securable, looking at the access, then writing a line of script to remove it?


texts: When do you know that you've made a design flaw in a SQL/relational database?

texts:
I've been working with a a relational model for a work project over the last few months, and I've gotten to the point where I'm wondering if I've made a mistake in my model's design or implemented things incorrectly. I can't go into specifics about the model because it's for work, but I'll try my best to elaborate.

The database contains data from three separate sources that are time sensitive. I.e., all of the date starts out with a DATE or DATETIME of some form. I've written a process to aggregate that data to unique values over date ranges and then run those unique values through an optimization engine in order to gain insights about an "aggregated optimal view" of the data. The process seems to work fine when there are relatively small amount of data (think ~170,000 records per date range that I select) but doesn't seem to be scaling upwards at all. Some of the queries that I've written are now taking much longer than they used to, and I think it's because of the way that I've designed the system.

Here are some of the "symptoms" of my database:

I use indexes in my tables, but haven't defined foreign key relationships

I was under the impression that have well defined indexes for each of my JOINs was sufficient

There are some queries where I have as many as 4 or 5 nested subqueries
Some of my queries JOIN 6 or 7 tables together in order to get data
A couple of queries that I have are >300 LOC

I could add a few more, but I think that this gets my idea across.

My question is: when does one know that their database isn't modeled properly?

Is it when your queries stop scaling? When you have to write long queries to get the data that you're looking for?

I can give more information about the stack and machine that I'm using if it helps.

Thanks


texts: Inherit default privileges: every table created by *any member of* role A is readable by role B

texts:
If I do this ...

ALTER DEFAULT PRIVILEGES
    FOR ROLE engineering
    IN SCHEMA reference
    GRANT SELECT ON TABLES to analytics;

I get ...

This syntax runs and has a valid meaning. The way it is implemented, any time the engineering role creates a table (in the reference schema), then any member of the reporting role will automatically have read access to it. But for that to work, you must be logged in or impersonating (set session authorization, set role etc) as the engineering role when you create the table.

I understand the rationale behind the way postgres implemented this. They want each role/user to set their own defaults. The default is a convenient shorthand so the user doesn't have to set permissions on every table they create. Postgres even allows someone to set the defaults for any role they can impersonate (and hence super users can set defaults for everyone). This is pretty powerful, but postgres didn't want to make it too powerful. They still wanted people to have to issue a separate command for each user's defaults. They wanted people to think hard about every user to whom they are giving this convenience.

But I want ...

But, I've already thought hard about it and I do want to set up those conveniences for a whole team. I want a single command where every user in the engineering team inherits these default privileges. Then, every time one of them creates a table, it would automatically be readable by every member of reporting. In fact, that's how I interpreted the syntax when I first found out I could include the FOR ROLE engineering clause.  But defaults are not inherited.

Alternatively, I would also be interested in a solution where no matter who (no matter what team they are in) creates a table in the reference schema, it will be readable by all the members of the reporting role. So in this variation, I can still open up the schema to reporting even if I can't (directly) control who gives those permissions (and why would I want to?). If I have this solution, I can still effectively limit it to the engineering team members if they are the only ones with create privileges in the reference schema.

Question

Am I missing something (such as an alternate syntax)? Or is there some other work-around?

  I am open to any solution (even building a custom extension) so long as it does not fall under one of the two categories below:

The solution doesn't really set a "default"

i.e. every time a member of the engineering team signs in or creates a table, they have to do some additional command to handle the permissions (such as change to the engineering role when they start the session)

The solution does not really inherit to the whole team

i.e. you have to set the default privileges for every team member in separate commands (and remember to do it whenever a new member is added

I've done my homework

None of the following questions (which Stack Exchange suggested based on the text of my question) has an answer for my nuanced need.

These questions are basic (didn't know about default permissions or how to use them as intended):

How to GRANT SELECT by default to B, on tables created by A?
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

These questions are similar to mine, but haven't been answered:

Default privileges granted only if table created by one superuser
How can I grant permissions on tables created in the future to members of a particular role?

These have some discussion of what I'm trying to do, but no solutions are offered:

Alter default privileges for role
PostgreSQL Role Configuration


texts: query that does functionality of listagg without use of it

texts:
I want to write a query so that it does functionality of listagg in oracle but I don't want to use it. Actually I don't want any function like lag and lead also. My table is-

Emp_id                          Emp_name                        Department
1                               bradley                             1
2                                david                              1
3                                 will                              2
4                                 john                              2
5                                 bale                              2
6                                 alan                              3
7                                 doug                              3
8                                 hagar                             4
9                                 carla                             4
10                                vashi                             1

I want output like this-

department                               emp_name
1                                        bradley,david, vashi
2                                         will ,john ,bale
3                                          alan, doug
4                                          hagar, carla

I have made a query for this but here I don't want to use sys_connect_by_path. My query is-

SELECT department,
       LTRIM(max(SYS_CONNECT_BY_PATH(emp_name,','))
       KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees
FROM   (SELECT department,
               emp_name,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) AS emp,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) -1 AS prev
        FROM   employee4)
GROUP BY department
CONNECT BY prev = PRIOR emp AND department = PRIOR department
START WITH emp = 1;

Can anyone help me?


texts: Optimize complicated sql query by either changing query or indexing

texts:
I'm not great with SQL and I started working at a place and they have a query that is running slow.  This SQL query is taking about 4 seconds to load and is coming from a table with about 160,000 rows.  It's a MyISAM table and we're using MySQL.  Here is the query:

$driver = 28;          //for some reason they're using a magic number here
$date = $theDate;      //the date is current date in datetime format

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
AND truck_id in
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%'
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' ))
and time like '$date%' ORDER BY customer, truck_id ASC;

Here is the jobs table structure:

id              int(11)
driver_id       int(11)
truck_id        int(11)
job_id          int(11)
mileage_start   int(11)
mileage_end     int(11)
mileage_total   int(11)
time            datetime
customer        int(11)
address         text
city            text
state           varchar(255)
zip             varchar(255)
phone           varchar(255)
...

I'm trying to get the query time to be much less than 4 seconds.  I just started at this place and changing the whole table structure might break a bunch of other stuff I'm not aware of yet.  Every column is indexed as BTREE (which I guess is the default?) so I'm wondering if what I need to do is either redo the query or index the used columns differently?  I'm not experienced enough with SQL to really know what to do.  Thank you for the input.


texts: Setup AlwaysOn with 2 Secondaries and not adding all the databases for 2nd Secondary

texts: I have a situation where we have AlwaysOn Setup in SQL Server 2016 Enterprise editions with primary in region A and secondary in region B setup in Asynchronous-Commit mode. We have 4 databases in participating in this AG. Now we would like to add all another secondary in same cluster in Region A to different server. Out of 4 databases we only want to add 3 databases to 2nd secondary which will be in region A. Last database is heavy write application which grows quickly and we do not want to sync it between 2 secondaries. Is it possible to achieve and is there any performance effect of it?


texts: Pros and Cons of SQL Server Database Mail

texts:
I'm planning to use SQL Server Database Mail.

I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new receiver records added to this table.

I would like to know pros and cons from the experts who already have used, or are aware of, this Database Mail feature.  Is this the correct system for sending mails, or is there better options to send mails from SQL Server?

Update: Above mentioned table is a kind of queue table for mails to be sent. There are many operations going on in the application and all these operations need to send mail once each transaction is complete. Hence, they store that information in this table. Later on, at every minute, we retrieve data from this table and send mails accordingly, marking that record as sent. I hope this better clarifies my requirements.


texts: Based on these DBCC SHOWCONTIG stats, should I index this table?

texts:
I have a SQL 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most accessed table and got the following results.

DBCC SHOWCONTIG scanning 'MYTABLE' table...
Table: 'MYTABLE' (12345678); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 738663
- Extents Scanned..............................: 92661
- Extent Switches..............................: 92720
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.58% [92333:92721]
- Logical Scan Fragmentation ..................: 0.35%
- Extent Scan Fragmentation ...................: 41.58%
- Avg. Bytes Free per Page.....................: 467.9
- Avg. Page Density (full).....................: 94.22%

Would rebuilding the index make sense on this table? Would there be any benefits to doing so?


texts: Oracle nested cursor gives poor execution plan

texts:
I have a relatively complex query which is returns top N results. On top of that, I have a nested cursor (on the outermost select as is only possible in Oracle). That outermost cursor works (or should I say, should work) with rows selected in top N query, but instead, it gets executed first.

In other words, nested cursor works with thousands of rows, instead of e.g. 20.

I know I can put what's in the nested cursor in a separate function, but I'm trying to figure out whether it's possible to improve this particular query somehow.

edit: Here's an example, disregard the fact that this CAN be rewritten in a better way, in my case, I actually do want a nested cursor if possible.

select outerTbl.*, cursor(select * from employees e where e.emp_id = outerTbl.emp_id) from (
    select * from
        (select m.* from managers m where m.emp_id < 100 and rownum <= :upperEnd) innerTbl
    where rownum >= :lowerEnd
) outerTbl


texts: Postgres Unnest or flatten a nested array inside of JSONB

texts:
I've got a table MY_TABLE that has the following columns:

User
Name
Data
Purchased

Data is of the form :

       [{
           "Id": 1,
           "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"[\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"Test123"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"]\\\\\\\\\\\\\\\"]\\\\\\\"]\\\"]\"]
        },
        {
           "Id": 2,
           "Attributes": []
        },
        {
           "Id": 3,
           "Attributes": ["[\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"Test2\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"]\\\\\\\\\\\\\\\"]\\\\\\\"]\\\"]\"]"]
        }]

However, what I want is:

    [{
        "Id": 1,
        "Attributes": ["Test"]
    },
    {
        "Id": 2,
        "Attributes": []
    },
    {
        "Id": 3,
        "Attributes": ["Test2"]
    }]

I believe the problem occurred when the script below was run more than once.

jsonb_set(
    mycol,
    '{Attributes}',
    case when js ->> 'Attributes' <> ''
        then jsonb_build_array(js ->> 'Attributes')
        else '[]'::jsonb
    end
)

I am unable to rollback the changes, so I have to remove the nested arrays manually. Does anyone know how I can go about doing so?

Instead of removing the nested arrays, I was thinking that I could extract the string and build a new array with that string as well. However, I am unsure of how to traverse the nested array to get the inner string


texts: data driven subscriptions in SSRS - pulling a password value from the database

texts:
If I create a data driven subscription in SSRS and use the query to return some value for the username and password, how "safe" is this password?  Let me explain.

I am looking to set up a data source on the SSRS Web Portal that uses Credentials stored securely in the report server and Use as Windows credentials when connecting to the data source.  This data source, in turn, would be used in the data driven subscription.  The user setting up the data driven subscription would not have access to the data source's credential password.

I would then like to permit the user to pull a username and password using this shared data source and then use in their subscription as the user/pass to save the file to a shared file area... without the user knowing what the user/pass is.

Make sense?  If so, is this a risky thing to do, security-wise?

(Ultimately I am thinking about using this as a way to consolidate the password for shared file area subscriptions into one place.)

Thanks!

Addendum 3/26 - I like to think the lack of feedback means this is a fantastic idea, but I'd rather get some validation that I'm not treading in dangerous territory here.  So let's try a rephrase:

Is there any way a user can view the raw results of the query associated with a data driven subscription?


texts:
So i accidentally made it so root can be accessed anywhere remotely how do i revoke the % privilege. Using mysql.

+-----------+
| host      |
+-----------+
| %         |
| 127.0.0.1 |
| ::1       |
| localhost |
+-----------+


texts: Why did my index become unusable after setting MAX_STRING_SIZE to EXTENDED?

texts:
I run Oracle Database 12c Standard Edition Release 12.2.0.1.0, and followed the official documentation to set MAX_STRING_SIZE to EXTENDED, to be allowed to use more than 4000 bytes in a VARCHAR2 column.

Running the utl32k.sql script produced two warnings:

Index "SCHEMA"."T01CALENDAR_CATEGORY" must be dropped because it now exceeds maximum key length.
Index "SCHEMA"."SYS_C007463" must be dropped because it now exceeds maximum key length.

This is surprising to me, because the column definition hasn't changed:

SQL> desc t01calendar
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROJID                    NOT NULL NUMBER(8)
 CATEGORY                  NOT NULL VARCHAR2(4000 CHAR)
 START_DATE                NOT NULL DATE
 END_DATE                  NOT NULL DATE

And T01CALENDAR_CATEGORY is simply an index created by:

CREATE INDEX "SCHEMA"."T01CALENDAR_CATEGORY" ON "SCHEMA"."T01CALENDAR" ("PROJID", "CATEGORY")

Why did this index suddenly become unusable, and do I have to drop it, or is there some other way to have an index on a VARCHAR2(4000 CHAR) column?


texts:
with this sample dataset

I have this query

SELECT products.id,
                        products.title AS product_title,
                        products.code,
                        products.created,
                        products.modified,
                        products.active,
                        attributes.title AS attr_title,
                        attribute_entries.title AS attr_entry_title
                        FROM products
                        INNER JOIN attributes ON attributes.model_id = products.id
                        INNER JOIN attribute_entries ON attribute_entries.FK_attribute_id = attributes.id WHERE ( products.id LIKE '%" . $model['value'] . "%' OR products.code LIKE '%" . $model['value'] . "%' OR products.title LIKE '%" . $model['value'] . "%') limit 10

For some reason I get duplicates in my result set.  I have done this type of query before but I never got duplicates.  Although this is the first time I query the id as well and treat it as string.  Is this my problem?


texts: How can I reduce time spent acquiring the mongodb schema lock?

texts:
We have a mongodb 4.0.10 cluster, backed by WiredTiger, in production, with a 3 node replica set consisting of a master and two slaves.  One of the slaves has another service co-located that queries the slave extensively.  In addressing some slowness in the co-located service I'm seeing a lot of surprisingly slow queries.  This one took 3.3 seconds:

  find: "myColl",
  filter: { myField: "myValue" },
  projection: { name: 1 },
  $db: "myDb",
  $clusterTime: { clusterTime: Timestamp(1568198047, 3), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } },
  lsid: { id: UUID("2ed823aa-e6af-4898-a4c1-c039d28a32ab") },
  $readPreference: { mode: "secondary" } }
  planSummary: IXSCAN { myField: 1 } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:232
  locks:{ Global: { acquireCount: { r: 1 } },
          Database: { acquireCount: { r: 1 } },
          Collection: { acquireCount: { r: 1 } } }
  storage:{ data: { bytesRead: 355, timeReadingMicros: 4 }, timeWaitingMicros: { schemaLock: 3284692 }

The line that stands out to me here, is the last one, indicating that it spends 99.9% of its time waiting to acquire something called a schema lock.

I checked this particular database and collection and it turns out the collection had 50 items at query time and the database itself was tiny (less than 1k documents in total).  Furthermore, there's also an index on myField.

Here's some other data about our particular usage of mongodb that might be relevant:

Multi tenancy by having a database per customer
Most documents are small
Most documents will have a similar size throughout their life cycle (I read some others here had performance issues related to padding and documents getting moved around as they grew in size)
Customer data grows through document count, not document size

I've been monitoring these slows queries for a while now and I can't see any pattern.  It's like mongodb is doing some maintenance task every so often and whatever query runs at that time is forced to wait.

Why is a read query waiting to acquire a schema lock?  What can I do to eliminate this long wait?


texts: Configure Oracle 11.2+ ACL to work with mail server having an internal IP address

texts:
I configured a new Oracle 11.2.0.4 database on CentOS 7.

I verified that I can send email from the database server 192.168.0.1's linux command line, using an 192.168.0.2 mail server (e.g. mail.example.com) on port 26.

The /etc/hosts file on the database server (192.168.0.1) contains the line

192.168.0.2 host9.example.com mail.example.com

so the database server can map mail.example.com to 192.168.0.2.

I'm trying to send mail using the following command in SQLDeveloper worksheet for schema1,

exec utl_mail.send(sender => 'name@example.com', recipients => 'name@example.com', subject => 'My Title', message => 'my body');

and it works. Then I use the same command minus the exec in a function called by a stored procedure, both in schema1, and it gives error

stack trace: java.sql.SQLException: ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at "SCHEMA1.MY_FUNC", line 260
ORA-06512: at "SCHEMA1.MY_PROC", line 40
ORA-06512: at line 1

My configuration is as follows. If I select * from dba_network_acls; as SYS I get:

192.168.0.2 26  26  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 443 443 /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 80  80  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 25  25  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39

If I SELECT acl, principal, privilege, is_grant, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM dba_network_acl_privileges; as SYS I get:

/sys/acls/http_permissions.xml  SCHEMA1 connect true    13-JAN-2017
/sys/acls/http_permissions.xml  SCHEMA1 resolve true    13-JAN-2017

If I select * from V$PARAMETER where NAME = 'smtp_out_server' as SYS I get;

3314   smtp_out_server 2   mail.example.com      mail.example.com  FALSE   TRUE    IMMEDIATE   TRUE    FALSE   FALSE   FALSE   FALSE   utl_smtp server and port configuration parameter        3948483925

If instead I use a proven mail package from 11.2.0.1 to send mail via UTL_SMTP in schema1, it fails with ORA-24247: network access denied by access control list (ACL) from both SQLDeveloper and function SCHEMA1.MY_FUNC. It fails on this line:

mail_conn := utl_smtp.open_connection('mail.example.com', 26);

Any idea what could be wrong?

UPDATE

If I replace mail.example.com with 192.168.0.2 when setting smtp_out_server so that select * from V$PARAMETER where NAME = 'smtp_out_server' returns

3314   smtp_out_server 2   192.168.0.2   192.168.0.2   FALSE   TRUE    IMMEDIATE   TRUE    FALSE   FALSE   FALSE   FALSE   utl_smtp server and port configuration parameter        3948483925

then the UTL_MAIL.SEND command from both SQLDeveloper worksheet and function give the error ORA-29279: SMTP permanent error: 550 Access denied - Invalid HELO name (See RFC2821 4.1.3).


texts: Get Total in a single line in SQL Server

texts:
I have a very simple SQL Query which is getting results in the following way

COLUMN A           COL-B

WINDCHILL   99.998889
WINDCHILL   96.000000

Now how i can modify my query so that I get the total in a single line?
COL-A         COL-B

WINDCHILL   195.99

The necessary query is as;

 SELECT
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      [IR.TimesheetHoursProjectTask] p
 WHERE
      p.ActualWork > 0 AND
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL)
 GROUP BY
      ISNULL([Task IT Assets], [Project IT Asset]), ActualWork
 ORDER BY
      p.ActualWork DESC


texts: SQL Server Data Tools and partition functions

texts:
I'm creating a sliding window loading scenario and the partition functions will be changing their boundaries through time.

I have created some partition functions in my SQL Server Data Tools (SSDT) database project with some initial boundaries hardcoded.

However, as time goes on and the partition functions' boundaries change, future SSDT database publishes will revert the boundaries to the original ones.

Is there some way to handle this scenario gracefully, possibly by disabling the publishing of the partition functions?

I've tried changing the Build Action property of the partition functions in SSDT, from the default Build, to None, but then the project fails to build due to a missing reference in the depending objects.


texts: How does Data Warehousing work in reverse?

texts:
I've made many researches about data warehousing to learn more about it and I think I've grasped the idea. But I've read in an article or two that data warehousing allows you to do analysis on data brought from operational databases and then in some cases, the results are sent back to the operational databases where some data is updated and then used.

Could someone give me an example where that might work?

Because what I personally understood is that data warehousing allows to analyse the data in order to determine sales profitability or find patterns in sales, etc.

So is what I've read true? If so, are there examples?


texts: Did the Change Tracking internals change from SQL Server 2008 to 2012?

texts:
In trouble shooting an issue with synchronizing disconnected devices with a central database server, we are experiencing a problem after upgrading to SQL Server 2012 on the server. It appears that the CHANGE_TRACKING_MIN_VALID_VERSION is returning a value 1 higher than it should (or at least than it did prior to the upgrade.)

I have been working thru Arshad Ali's great walk thru example of how to set up a simple example.

I have run the scripts from #1 thru #5 to insert, delete, and update a row in the Employee table in both a SQL Server 2008 and a 2012 environment.

In 2008, the following statement returns a 0:

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Employee'))

In 2012, it returns a 1.

In working thru a few more scripts (6-8) in the tests, I set the retention period to 1 minute to hopefully force a cleanup action.  I left for the day and apparently it ran overnight.

In the 2008 instance, the CHANGE_TRACKING_CURRENT_VERSION and the CHANGE_TRACKING_MIN_VALID_VERSION are equal (11).  In the 2012 instance, the CHANGE_TRACKING_MIN_VALID_VERSION is one higher (12) than the CHANGE_TRACKING_CURRENT_VERSION (11).  This could have an impact to the synchronization process when a database is idle for extended periods of time.  And we have found that process could get caught in a loop, especially when the following test is performed to determine if a re-initialization, as opposed to synchronization, is required:

IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Employee')) > @sync_last_received_anchor
       RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''...

Has anyone else experienced this change in behavior? Does anyone have an explanation?


texts: Access .sql script files on other partition or hdd

texts:
I'm running a Linux Mint 19.1 - Tessa OS, which for you who might not know it is based on Ubuntu 18.04 - Bionic Beaver. On this I have installed postgresql 11 which work well. There is one problem though. For organisational reasons code files are stored in project directories on a separate partition of my hdd. These directories, or actually the complete partition can not be accessed from within the psql environment, even though the user account has full access to the partition normally.

To begin with, auto-completing paths with tab doesn't work for the partition and typing the path manually gives a Permission denied message. I've tried adding a su or sudo argument before the \i but it doesn't work either. Nor does starting the session from within the partition.

E.g.

user@laptop:/$ sudo -u postgres psql
[sudo] password for user:
psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
Type "help" for help.

postgres=# \i /media/user/Storage/Projects/DB_project/script1.sql
/media/user/Storage/Projects/DB_project/script1.sql: Permission denied
postgres=# su \i /media/user/Storage/Projects/DB_project/script1.sql
/media/user/Storage/Projects/DB_project/script1.sql: Permission denied
postgres-# ;
ERROR:  syntax error at or near "su"
LINE 1: su
        ^
postgres=#

For now I have to copy the files I want to use to my /home/ directory, or any directory in that tree, and then make sure to paste any updated files back in the Projects directory tree.

Any ideas on how to solve this so I don't need to copy files back and forth all the time?


texts: Cardinality with specialization in enhanced entity-relationship diagrams

texts:
I'm currently taking an introduction course to databases and I've come across a problem not described in the textbook.

I'm wondering about the use of cardinality with the use of specialization in enhanced entity-relationship (EER) diagrams.

The case is that I wish to have e.g. a Team which has to have one and only one leader (a designated role). However the Team also consist of other roles. In total each Person can only have one role in the team.

I can't seem to figure out how to draw this up from the description of EER diagrams in my textbook. Am I, by the standardization, allowed to draw min-max pair for the arrows in specializations? Or is there another way to make conditional relationships.


texts: Can I restore database A to database B but keep B's permissions?

texts: I have a live db A and a test db B. The live db is backup'd every day. And I use A's backup file to restore to B to keep in sync. But then B's permission setup is also overwritten by A's. Can I only restore A's data but keep B's permission?


texts: Copy Table from one MySQL server to another

texts:
Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance.

I mean the table is very large and using mysqldump or any select query has bad consequences.

Overall my question is: if there is a way to get the copy of the production database or table without performance problems on production.


texts: How to use Weekday() function inside join in MySQL?

texts:
I have 2 tables from which one is a parent & other one is child.For 1 order we have multiple records in child table.
For e.g: Order & order_frequency are 2 tables.
Order table data:
+-----+---------+---------------------+
| id  | product | orderDate           |
+-----+---------+---------------------+
| 100 | abc     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+
| 200 | xyz     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+

order_frequency table data:
+----+-------+-----------+
| id | ordID | frequency |
+----+-------+-----------+
| 1  | 100   | 111       |
+----+-------+-----------+
| 2  | 100   | 234       |
+----+-------+-----------+
| 3  | 200   | 111       |
+----+-------+-----------+
| 4  | 200   | 234       |
+----+-------+-----------+

I want to fetch matching frequency from order_frequency table based on order date(Which are falling on monday[111] & Tue,wed,thru[234]).
I have tried below query:
SELECT * FROM order LEFT JOIN order_frequency ON (order.id=order_frequency.ordID AND order_frequency.frequency like "%1%");

when i tried above query by providing hard coded value for frequency in column it provide me expected result.
But i want to fetch it dynamically by providing orderDate.
something like below:
SELECT (weekday(DATE(orderDate))+1) AS weekday,* from order LEFT JOIN order_frequency ON (order_frequency.Day like CONCAT('%',@weekday,'%'))

Thanks in advance.


texts: Splitting existing MongoDB cluster into two clusters with minimal downtime

texts:
We have one MongoDB cluster with 3 shards. Each shard consists of 5 replicas, one of replicas is hidden. Only one very large and active collection is actually sharded, everything else is located in first shard and not sharded. We want to split this cluster into two separate clusters leaving only the sharded collection in the original, first cluster and moving everything else from first shard to new second cluster. The size of unsharded collections is roughly 300-400 GB.
Everything is deployed on AWS EC2 instances. Also we need to completely stop new writes from application servers while we performing migration.
We've thought of some strategies to do that:

mongodump/mongoexport - very time-consuming process, can't really afford server downtime coming from it;
snapshot replica EBSs and restore to new instances to form a new cluster - again, very time-consuming due to EBSs sizes;
to steal a replica  - prepare config replica set and mongos for new second cluster, detach one replica from first shard, relaunch it as a separate replica set and attach it as a shard to the second cluster.

The 3rd one is quickest strategy, I think, although there is a drawback in this case - new cluster is a shard that will be consist of 1-node replica set that may be not enough to perform writes and reads normally. We can quickly add 2nd and 3rd replicas but, again, it may hinder the cluster even harder.
Do you have similar experiences with collections migrations and cluster splitting? Which strategy will give the least possible downtime?


texts: Storing reports with a lot of rows in relational database (Oracle)

texts:
I am setting up a relational database (Oracle) to store some reports with the following specifications:

Each report has the same columns (say 20 columns of strings or numbers). I expect the columns to be rarely changed but they might change.
Each report has a lot of rows (~200k).
The report generation frequency is like once every few days.

I have this schema in mind where report_id and row_id is the composite primary key:

+---------------+---------------+-----+
|    Column     |     Type      |     |
+---------------+---------------+-----+
| report_id     | uuid (string) | KEY |
| row_id        | number        | KEY |
| data_column_1 | string        |     |
| data_column_2 | number        |     |
| ...           | ...           |     |
+---------------+---------------+-----+

To retrieve a report, I will do something like

SELECT * FROM report_rows WHERE report_id = 'SOME-UUID';

Is this an appropriate design? Will performance suffer when there are more reports? If so, is there a better schema / other database to store data like this?

Thanks.


texts: T-SQL query to show table definition of sys.time_zone_info

texts:
Following query could be used to show definitions of custom table.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PostHistory'

not work for sys tables

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sys.time_zone_info'

I guess the info is in another table in another database what are they?

I've also tried sp_help 'sys.time_zone_info', and got

  The EXECUTE permission was denied on the object 'sp_help', database 'mssqlsystemresource', schema 'sys'.


texts: Columnstore index on multi-tenant database

texts:
I have a sql azure database that is used only for analytics.

It is constantly being updated with data pushed from the OLTP system.

The OLTP inserts/updates/deletes are pushed via webhooks to a service that propagates the changes into the analytics db in real time.

As a result, we have a db that must sustain both a OLTP and analytics work loads, althought the analytics queries performance is more important.

I haven't used columnstore indexes before, but after doing minimal research it seems like this type of payload can be good fit for nonclustered columnstore indexes. The clustered index would stay rowstore, for fast insert/update/delete.

But, I have an extra requirement that I haven't seem mentioned: multi-tenancy.
When users use our analytics app, they are always associated with a single tenant id, and the analytics queries will always have a WHERE clause on the tenant id. The tenant id column is highly selective. The biggest tenant makes up for less than 5% of all rows and most tenants make up for less than 1% of rows.

Is it still a good idea to use NCCI indexes for multitenants database? If so, should I set up my index in a particular way?


texts: How to create a citation/quote from a Postgres text field?

texts:
I have a database with a lot of text and I want to find the surrounding text for a word that I am searching for i.e. If I search this text for "text" I should get something like (and more consistent than):

.. lot of text and ..
.. the surrounding text for ..
.. this text for "text"..
.. for "text" I should ..

I can write this in a program but I wondered if there is a good SQL trick to get something approaching this with just a query for a quick and dirty report.


texts:
I have quite big datapump dump file and tried to load it into compressed tablespace that is created with "DEFAULT COMPRESS FOR OLTP" because of lack of free space. Anyway, when I look at "compressed" dba_segments and compare them with source segments they have the same size.
How does this compress feature work? I expected lower size of compressed tables but it seems doesn't work this way. P.S. Datapump job is not finished due to no free space left.


texts: One and only one relationship - ER diagram to relational schema

texts:
I'm taking this term a DB course as part of my CS studies.
This week we began studying the subject of ER diagrams, which I find a bit confusing.
I have some uncertainty about the picture above (Note: the rounded arrows indicate "One and only one" - that is, every subject from B can and must be directed to exactly one subject in A, and vice versa).
I have to create a suitable relational schema for this diagram, and here is my try:

A(a, b, c, d) (b is the key)
B(e, f) (e is the key)

This is my uncertainty:
R(b, e) - no matter which key I'm creating out of those foreign keys, the "one and only one" constraint isn't held.
How can I create the schema where this constraint holds?
What about if you had "at most one" arrows, and not exactly?
How the answer would change?


texts: SQLITE - Update multiple columns, in multiple rows on a table with a composite PK

texts:
First of all, a question:
UPDATE table_a
    SET column_d = 100, column_e = 200
    WHERE column_a = 1 and column_b = 0 and column_c = 1;

UPDATE table_a
    SET column_d = 123, column_e = 150
    WHERE column_a = 2 and column_b = 1 and column_c = 3

...

Imagine this goes on.
Would any performance improvement come from merging these in a single query?
If so, how can it be done?
Remember I am asking specifically for SQLlite3


texts: Can I surveillance the backup taken in SQL Server?

texts: I have a SQL Server and I want to put some surveillance on it, to know if someone is taking a backup. I know that I can make an audit to monitoring the backup. But I want to have some kind of trigger every time someone takes a backup, so I can send an email to myself about it. Is it possible? (To get an email every time a backup is taken.)


texts: Use DBSNMP for Database Usage Tracking Credentials without adding manually

texts:
Enterprise Manager Cloud Control 13c Version 13.3.0.0.0
Oracle BI Publisher 12.1.3.0.0
For each target we have use DBSNMP credentials.
We have two type of credentials for each target:

Monitoring Database Credentials (for agent monitoring purpose)
Database Usage Tracking Credentials (for BI Publisher report)

Monitoring Database Credentials already set up when we create target. Also when we go to
Setup – Security – Monitoring Credentials
we see that Database Usage Tracking Credentials is empty instead of Monitoring Database Credentials.
We could set Database Usage Tracking Credentials with DBSNMP user/password as well.
But we have situation when we have thousand of DBs and we don’t know DBSNMP most password. It is created by automate procedure. Also on some DBs this password could be different. And also could be change in future.
So it is not possible to set up Database Usage Tracking Credentials manually.
But we need this credentials to generate BI Publisher Report - Database Usage Tracking Summary.
DBs without credentials does not includes in this report.
My question is – could we set up that OEM could use same DBSNMP credentials for both: Monitoring Database Credentials and Database Usage Tracking Credentials? Without using console or emcli.


texts: Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS?

texts:
Is it possible to connect to the DAC using SSMS?
If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message:

  Dedicated administrator connections are not supported via SSMS as it
  establishes multiple connections by design.
  (Microsoft.SqlServer.Management.SqlStudio.Explorer)


texts:
I am restoring several MariaDB databases into a Amazon AWS RDS MariaDB instance from an AWS EC2 machine in the same VPC / region etc.

I am using a shell script like so:

mysql -u root -h hostname.amazonaws.com -ppassword < db1.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db2.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db3.sql
...

The sequence is important since the later databases have VIEWS and JOINS that reference earlier databases. I have figured that part out and doing the DBs one at a time manually works as expected.

The issue is, when I chain the DBs in a single shell script, the later restore for, say DB2, complains that some table in DB1 does not exist. The exact error message is below:

  ERROR 1146 (42S02) at line 482: Table 'db1.table_name' doesn't exist

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

Is there any way I can get the earlier SQL files to block till it completes successfully or fail and break subsequent SQL files from executing?

Client and server info below:

mysql client  Ver 15.1 Distrib 10.0.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Server version: 10.0.31-MariaDB MariaDB Server


texts: Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail?

texts:
According Tom Kyte on editioning views "you can put triggers on it, do to it things you would do to a table". However, my triggers on my editioning views in my custom schema don't fire. Tom doesn't get things wrong, so I must have something configured incorrectly. Any suggetions?

Create Table, Sequence and Trigger:

CREATE TABLE "MY_SCHEMA"."MY_TABLE"
   ("TABLE_KEY" NUMBER(10,0) NOT NULL ENABLE,
   "FIELD2" VARCHAR2(32) NOT NULL ENABLE,
   "FIELD3" NUMBER NOT NULL ENABLE);

CREATE SEQUENCE  "MY_SCHEMA"."MY_SEQUENCE"  MINVALUE 1
    MAXVALUE 1000000000000000000000000000 INCREMENT BY 1
    START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER "MY_TABLE_TRIGGER"
  BEFORE INSERT
  ON  "MY_SCHEMA"."MY_TABLE"       FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;

Update Editioning:

exec ad_zd_table.upgrade('MY_SCHEMA', 'MY_TABLE');

That will create an editioned view that looks like this:

CREATE OR REPLACE FORCE EDITIONING VIEW "MY_SCHEMA"."MY_TABLE#" ("TABLE_KEY", "FIELD2", "FIELD3") AS
  select TABLE_KEY TABLE_KEY, FIELD2 FIELD2, FIELD3 FIELD3 from "MY_SCHEMA"."MY_TABLE";

And it will modify the trigger so it looks like this:

CREATE OR REPLACE TRIGGER "MY_SCHEMA"."MY_TABLE_TRIGGER"
  BEFORE INSERT

 ON  "MY_SCHEMA"."MY_TABLE#"        FOR EACH ROW
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.table_key FROM dual;
END my_table_trigger;

Now we test:

INSERT INTO my_table
  (field2,
   field3)
VALUES
  ('TEST', 1234)

Result:
    ORA-01400: cannot insert NULL into (“MY_SCHEMA”.MY_TABLE”.”TABLE_KEY”)


texts: Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)

texts:
I want to fetch up to 100 rows based on their id. The id is the primary key of the table.

The query that I had written looks like this:

select * from table where id = any ($1);

where $1 is interpolated as an array of ids.

When using EXPLAIN ANALYZE I get the following plan (explain link):

                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..44.98 rows=17 width=553) (actual time=100.048..834.209 rows=17 loops=1)
   ->  Index Scan using instagram_id_index_1000 on profiles_1000  (cost=0.43..44.98 rows=17 width=553) (actual time=100.046..834.163 rows=17 loops=1)
         Index Cond: (id = ANY ('{34491540,28977916,33241270,33609141,31043380,29364420,30247037,33311491,36267571,32886281,32366574,32569254,33038689,31089076,29416100,30455309,31570597}'::integer[]))
 Planning time: 424.512 ms
 Execution time: 834.280 ms
(5 rows)

When I actually execute it (with \timing) I get results in the 2-5 seconds range! I really cannot wrap my mind around such abysmal performance. The execution time provided by EXPLAIN ANALYZE was already high in the first place.

Some context:
1) the database is local, so there is no network latency
2) the table I'm querying on is a materialized view
3) I also tried the where id in (...) variation and nothing changed
4) I tried to programmatically loop over the ids and run a separate query for each and it produces better results (around 1.5s)

Is there something that can be done here? I cannot believe that Postgres' performance in my case is this bad. The server also has 8 cores, is it possible to parallelize this query as much as possible?


texts:
I am using the postgis function ST_Contains to find all of the census blocks in different counties, the counties have been split by congressional district boundaries. I am trying to find the population of each county part that has been split. I used this sql statement...

  SELECT cp.state, cp.countyfp, cp.county, cp.cd, sum(bd.pop00)
  FROM countyparts2008_split_v2 AS cp
  JOIN us_block_2000_with_data AS bd
    ON ST_Contains(cp.geom, bd.geom)
    AND cp.state = '01'
  GROUP BY cp.state, cp.countyfp, cp.county, cp.cd

and i get this result

"01";"115";"St. Clair";"04";60306
"01";"125";"Tuscaloosa";"06";161992
"01";"125";"Tuscaloosa";"07";161992
"01";"115";"St. Clair";"03";60306
"01";"115";"St. Clair";"06";60306
"01";"073";"Jefferson";"04";647119
"01";"025";"Clarke";"01";27178
"01";"025";"Clarke";"07";27178
"01";"037";"Coosa";"02";10411
"01";"101";"Montgomery";"03";221731
"01";"103";"Morgan";"04";108021
"01";"107";"Pickens";"04";20143
"01";"103";"Morgan";"05";108021
"01";"125";"Tuscaloosa";"04";161992
"01";"037";"Coosa";"06";10411
"01";"107";"Pickens";"01";20143
"01";"107";"Pickens";"06";20143
"01";"107";"Pickens";"07";20143
"01";"107";"Pickens";"03";20143
"01";"037";"Coosa";"03";10411
"01";"073";"Jefferson";"06";647119
"01";"073";"Jefferson";"07";647119
"01";"101";"Montgomery";"02";221731

The numbers for each county part should be different, so for example each St. Clair entry should have a different population total (the last column). I figure I am aggregating it wrong or something, but not sure exactly.

I also tried this sql query

SELECT cp.state,
       cp.countyfp,
       cp.cd,
       (SELECT SUM(pop00)
          FROM us_block_2000_with_data AS bd
         WHERE ST_Contains(cp.geom, bd.geom)
         GROUP BY cp.cd, countyfp, state)
  FROM countyparts2008_split_v2 AS cp
 WHERE cp.state = '01'


texts:
I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.
This is not a store proc , he is doing it manually from his machine to get data and later after extraction just push it to other application for data visualization
Below is the Query i tried to create which is similar to my user query, logic wise. But since i am still figuring out how to run ETL package on WideWorldImporter database to get it up to date, cant really repro the run time.

For my env: Fact table is around 6 TB. What i have noticed is around
10K rows fetched is varying between 10-30secs while anything over 60K
rows goes over 10 mins

Query below
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Select * from  [Fact].[Transaction] f
INNER JOIN  [Dimension].[Customer] dc  on dc.[Customer Key] =f.[Customer Key]
INNER JOIN  [Dimension].[Date] dd on dd.[Date] =f.[Date Key]
INNER JOIN  [Dimension].[Supplier] ds  on ds.[Supplier Key] =f.[Supplier Key]
INNER JOIN  [Dimension].[Transaction Type] dt  on dt.[Transaction Type Key] =f.[Transaction Type Key]
INNER JOIN [Dimension].[Payment Method] pm on pm.[Payment Method Key] =f.[Payment Method Key]
WHERE f.[Date Key]>= convert(varchar,getdate(),112) and
f.[Lineage Key] between DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP) -300 AND DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP)
ORDER BY [Transaction Key]

Actual query has inner join to almost 10 more tables.

Lineage_key is INT NOT NULL and we have NC index on this column as the only leading key
Index:-

Is there something i can do with Query tuning as index look good?
Plan:- I can paste the part of plan as captured, rest it cant fit due to 30 odd inner joins.

Edit-
I tried to look at the plan which is too complicated chain as there are 30 odd inner joins. So would try my best to paste. But i found something interesting. I am seeing that compile time is around 8 secs in total of 14 sec run. So i think problem seems more on very high compile time. Is there a way we can avoid compilation time when query has joins with so many tables?
Ran wait stats for 10 min interval when same query ran 8 mins and other time  22 secs. Both queries i ran from SSMS.


texts: MongoDB as a log storage. Choosing shard key

texts:
I'm designing a log storage system based on MongoDB.
I want to shard a log collection to increase ingestion and capacity (distribute writes to several machines) while allow fast search.
I should be able to increase ingestion by adding more nodes to the cluster.

My collection has following fields:

Subsystem - string, name of the application. E.g: "SystemA", "SystemB". ~ 100 unique values.

Tenant - string, the name of the deployment. It's used to separate logs from different application deployments / environments. E.g: "South TEST", "North DEV", "South PROD", "North PROD". ~ 20 unique values.

Date - timestamp.

User - string.

SessionId - guid, logically groups several related log records.

Data - BLOB, contains zipped data. Average size = 2Kb, maximum = 8Mb.

Context - array of key/value pairs. Both key and value are strings. It's used to store additional metadata associated with event.

The search could be performed by any combination of fields Subsystem, Date, User and Context.
Tenant almost always will be specified.

The question is - what shard key and sharding strategy will be better in that case?

My suggestions:

The simplest case is to shard by Tenant, but it will cause highly uneven data distribution, because PROD environments generates much more logs than DEV.

"Tenant + Subsystem" seems to be better but still there are subsystems that generates much more logs than other subsystems.
And also subsystem is not mandatory - user can omit subsystem during search and search query will be broadcasted.

"SessionId" will cause even data distribution but search requests will be broadcasted to all nodes.


texts:
Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME respectively and most of my dates, which had DD >= 13 to 0000-00-00.

Now, I am left with a backup I took 3 months ago (~77400 rows) and then the newer ones. Is there any way that I can restore the older dates?

The way I see it, I'll have to try updating the dates using the older .SQL file for those 77400 rows and after that will have to write a script to check and increment date with each passing 24 hour span in the TIME column.

I didn't have a backup taken after 12th April and didn't consider taking one while altering the database. :(

EDIT

  mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` VARCHAR(50) NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` VARCHAR(50) NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

I changed the table code from that to this:

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` TIME NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685


texts: Using Extended Event For Tracking Updates To A Table

texts:
I have a table that keeps getting updated, and no one can figure out where the updates are coming from.  I suspect they are coming from Entity Framework, but I want to capture the UPDATE and associated information via an Extended Event to prove this theory.

I'm on SQL 2014 Enterprise, and trying to capture that information using the exec_prepared_sql and sql_statement_starting events.  Here is what I have so far:

CREATE EVENT SESSION [Query Trace] ON SERVER
ADD EVENT sqlserver.exec_prepared_sql(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123)))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\Query-Trace.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

This seems to work well, I have tested it and been able to captured both a regular UPDATE statement and one called with sp_executesql.  However, I am still missing something. The data in the table is still getting modified and this Extended Event is not capturing the UPDATE that does it.

So, my questions are:

1) Is there something else that I need to be watching in my Extended Event in order to capture UPDATES possibly coming from Entity Framework?

2) Is there something else I should be using instead of Extended Events for this?

Thanks!


texts: Domain Independent Cluster Error "Cluster network name resource..."

texts:
Windows Server 2019
SQL Server Standard 2019
3 Basic AG with listeners
Setup: I have a domain-independent two-node cluster with a file share witness. Our nodes are not joined to a domain. I continually get the following error:
"Cluster network name resource failed registration of one or more associated DNS name(s) because the a DNS server could not be reached.
Cluster Network name: 'Cluster Name'
DNS Zone: 'ag.domainsuffix.com'
DNS Server: '1xx.xxx.x.x8,1xx.xxx.x.x9'
Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.
What's also odd is that the Cluster Network name shows up simply as 'cluster Name'
Most resources say to modify the A record on DNS which I don't think is applicable for this setup.
Has anyone encountered this?


texts: Cannot connect to an Hana database via its ODBC driver

texts:
I have not found any documentation about ODBC connection strings for Hana and am trying to connect using the examples in SAP HANA Database Client Interfaces.

An attempt to connect via ODBC with the connection string:

DRIVER=HDBODBC; SERVERNODE=192.168.0.213:30015; ID=SYSTEM; PWD=PASSWORD;

fails with this error:

ERROR [08S01] [SAP AG][LIBODBCHDB DLL][HDBODBC]
Communication link failure;
-10709 Connection failed (RTE:[0]  (192.168.0.213:30015))

whereas the corresponding ADO.NET connection works:

Server=192.168.0.213:30015;UserName=SYSTEM;Password=PASSWORD;

What may be wrong with the ODBC connection? I have the correct driver installed.


texts: How can I get the measures of the last month with MDX?

texts:
I would like to get the values of the measures of the previous month.

That's what I have:

SELECT
   [Measures].[Recuent distinct] ON ROWS,
   TAIL(NonEmptyCrossJoin([Dim Date].[Calendar].[Month Name].Members,1),1) ON     COLUMNS
  FROM [BDID]
  WHERE [Dim Canal].[Canal].&[Data]

Result:

October 2015
    Recuent Customers 10344

That's what I would like to have:

September 2015
Recuent Customers 20754

The thing is that my MDX takes the values of the current month, but I want to take the values of the last month. It has to be valid for each month, I mean I would like to have an automatic query for using on Power BI. It means, this month It has to gave me the values of the last month (September) and next month the values of October, in order to avoid the modification of the query each month.

Let me know if you need more information.


texts: Choice of connection pooling library for VM deploys

texts:
HickariCP docs say:

  HikariCP relies heavily on accurate high-resolution timers for both
  performance and reliability. It is imperative that your server is
  synchronized with a time-source such as an NTP server. Especially if
  your server is running within a virtual machine. Do not rely on
  hypervisor settings to "synchronize" the clock of the virtual machine.
  Configure time-source synchronization inside the virtual machine. If
  you come asking for support on an issue that turns out to be caused by
  lack time synchronization, you will be taunted publicly on Twitter.

Which other connection pooling libraries would you choose, which are less sensitive to virtual machine time drifts?


texts:
I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure.

I define it as follows:

create table #tempDateTable
(
    Name varchar,
    TempDate datetime
)

INSERT INTO #tempDateTable(Name, TempDate) values ('Smith', @myDate)
SELECT * FROM #tempDateTable

@myDate is parameter passed to the stored procedure.

When I execute the stored procedure it gives me:

Msg 8152, Level 16, State 14, Procedure date_samples_procedure, Line 90
String or binary data would be truncated.

What I find strange is that Line 90 is not any of the above shown lines, but if I remove all the code for this #tempDateTable, the stored procedure works normally.


texts: Improve performance of count(*) with two exists

texts:
I need some help improve performance of this query that runs in Postgresql 9.4 at Amazon RDS:

SELECT count(*) AS total
FROM people
  WHERE EXISTS
    ( SELECT 1
     FROM person_values
     WHERE (organization_id = 1 OR organization_id IS NULL)
       AND person_id = people.id
       AND deleted_at IS NULL
       AND active = TRUE)
  AND EXISTS
    ( SELECT 1
     FROM alexandria.person_values
     WHERE (organization_id = 1 OR organization_id IS NULL)
       AND person_id = people.id
       AND active = FALSE);

Time: ~7-15 seconds

Explain analyze: http://explain.depesz.com/s/Ytla

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=34.80..34.81 rows=1 width=0) (actual time=0.017..0.017 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=22.73..34.80 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)
         Join Filter: (person_values.person_id = person_values_1.person_id)
         ->  Nested Loop  (cost=13.59..21.63 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1)
               ->  HashAggregate  (cost=13.16..13.17 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
                     Group Key: person_values.person_id
                     ->  Bitmap Heap Scan on person_values  (cost=9.14..13.15 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
                           Recheck Cond: ((organization_id = 1) OR (organization_id IS NULL))
                           Filter: ((deleted_at IS NULL) AND active)
                           ->  BitmapOr  (cost=9.14..9.14 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
                                 ->  Bitmap Index Scan on alexandria_person_values_organization_id_index  (cost=0.00..4.57 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                                       Index Cond: (organization_id = 1)
                                 ->  Bitmap Index Scan on alexandria_person_values_organization_id_index  (cost=0.00..4.57 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                       Index Cond: (organization_id IS NULL)
               ->  Index Only Scan using people_pkey on people  (cost=0.43..8.45 rows=1 width=4) (never executed)
                     Index Cond: (id = person_values.person_id)
                     Heap Fetches: 0
         ->  Materialize  (cost=9.14..13.16 rows=1 width=4) (never executed)
               ->  Bitmap Heap Scan on person_values person_values_1  (cost=9.14..13.15 rows=1 width=4) (never executed)
                     Recheck Cond: ((organization_id = 1) OR (organization_id IS NULL))
                     Filter: (NOT active)
                     ->  BitmapOr  (cost=9.14..9.14 rows=1 width=0) (never executed)
                           ->  Bitmap Index Scan on alexandria_person_values_organization_id_index  (cost=0.00..4.57 rows=1 width=0) (never executed)
                                 Index Cond: (organization_id = 1)
                           ->  Bitmap Index Scan on alexandria_person_values_organization_id_index  (cost=0.00..4.57 rows=1 width=0) (never executed)
                                 Index Cond: (organization_id IS NULL)
 Planning time: 0.500 ms
 Execution time: 0.092 ms


texts: Use a set database schema or EAV design?

texts:
I have already created a database design with a set schema.
The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders like Gravity Form have been tried and suitable in this case.

I have about 15 tables (mostly in 2NF and 3NF) with set attributes and datatype.

But the question is should I stick with the traditional normalized schema design or use EAV design instead for some tables? It seems Gravity Form uses EAV DB design but that is due to the dynamic nature of a form builder.

This question has come up due to the fact that later on a attribute may need to be added, removed or updated from one or set of tables. The fields in the quotation form are reasonably standard with research done and I don't imagine modifying the fields and hence the attributes in the DB often.

I have read there are concerning pitfalls of EAV design. So if the fields are not going to changed often then is a set schema design with relationships useable?

Also in the event of modifying the schema is ALTER table going to cause any issues? If one adds a attribute to a table with ALTER table what would the previous data sets in the table have the value of the new attribute?
What are the alternative of ALTER table?


texts: Why my BEFORE UPDATE trigger takes values from the NEW record?

texts:
I'm updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and creates a second one. On save the result is stored in the table table1 as 2 records, one with the original id (the PK) and the other with a new id. But I need to keep the original polygon (for history) and I try to do this with a trigger function that simply should make a copy of the original record from table1 to another table table2, totally similar to the first one, except that I removed the SERIAL and the PK.

My trigger:

CREATE TRIGGER area_reduc_1
    BEFORE UPDATE
    ON table1
    FOR EACH ROW
    WHEN ((pg_trigger_depth() < 1))
    EXECUTE PROCEDURE save_area_before_upd();

My trigger function is:

CREATE OR REPLACE FUNCTION field_survey.save_area_before_upd()
    RETURNS trigger AS
$BODY$
BEGIN

IF (TG_TABLE_NAME = 'table1') AND (TG_OP = 'UPDATE') THEN

    INSERT INTO table2
    SELECT DISTINCT *
    FROM table1 d
    WHERE NEW."id" = d."id" AND NEW.area_ref = d.area_ref
    ;
END IF;

RETURN NEW;

END;
$BODY$

BUT in table2 I end up with attribute values that are the ones I recorded in QGis, i.e. the ones coming from NEW! Why and how can I solve this?

I use QGis 2.8.2 (but that should not matter) and PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit.


texts: Could not continue scan with NOLOCK due to data movement SQL Server 2005

texts:
I am working on a sql database and it got corrupted due to power failure while I am doing some data entry on the system.

I set my database to emergency mode so that I could get some data out of it before dumping the database but I keep getting the error:

  Could not continue with NOLOCK due to data movement

I set the database to offline mode and now when i try to turn it online.It is giving me this error

Set offline failed for Database 'xyz'.

The log scan number (1224:41:1) passed to log scan in database 'xyz' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'xyz' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Database 'xyz' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9003)


texts: SQL-Server: What is a good way to aggregate on different columns within the same query?

texts:
I'm using a SQL query to generate a count of row repeats. The SQL query looks like this:

SELECT
  Category,
  COUNT(Category) AS Count
FROM
  TableA
GROUP BY
  Category

Which results in a table that looks like this:

Category  | Count
typeA     | 4
typeB     | 6
typeC     | 1
etc       | 9

Is it possible, within the same query to have an aggregation of the Aggregated rows? I would like to sum the Count column to get a percentage of each Category's contribution to the total. I.e. I'd like to produce the following table:

Category  | Count  | Perc of Total
typeA     | 4      | result of (4/20)*100
typeB     | 6      | result of (6/20)*100
typeC     | 1      | result of (1/20)*100
etc       | 9      | result of (9/20)*100


texts: Multiple BindIp in MongoDB 3.4.1 on Debian Jessie

texts:
I just upgraded from MongoDB 3.2 to 3.4.1.

/etc/mongod.conf had a multiple BindIP:

bindIp: [127.0.0.1,192.168.20.1]

This is not working anymore.
I get this error :

Scalar option 'net.bindIp' must be a single value

According to the documentation, It must be correct :
https://docs.mongodb.com/manual/reference/configuration-options/#net.bindIP

Any idea ?


texts: How to efficiently store impressions in db?

texts:
For my flask app I need to track impressions, clicks on images. The current implementation that I have is impressions table where I store impression ip, useragent, geo location and all the relationships that I would like to have. So every impression has it's own row. Mainly I'm interested in count of impressions by hour, day, date range, month.
I tested this implementation by inserting 2.5M rows in my impression table. The problem with this implementation is that count query are slow when I try to get impressions count for whole month.(5 seconds per query) Index doesn't help much here. I tried different query's and MySQL db optimization and results are not satisfactory.
In my opinion I will have to change the way I handle impression tracking. I was thinking to create a table that holds impressions count for every hour. When ever new impression happens I would just increment counter for that hour.
But before I go and rewrite bunch of code and make mistake again.
I'm asking here how other web app handle tracking impressions and get results so fast? Or what is the correct way to store impressions?
Hopefully some can guide me to fix this problem.
Best regards


texts: Oracle audit: Only 'SESSION REC' in ACTION_NAME column

texts:
I was asked to generate a report of operations performed on certain Oracle 9i tables in a given period of time.

When I query the DBA_AUDIT_TRAIL table I only get SESSION REC in the ACTION_NAME columen.

I was expecting SELECT, INSERT, DELETE etc.

What am I doing wrong?
What does SESSION REC mean?


texts: Is query executed when mysql server has gone away?

texts:
How can I be sure that a query was not executed?

For example, I try to execute

update `table` set `a`='b'

and got the error 2006 server has gone away


texts: Migrating from a physical database to a cloud database

texts:
What are the implications of moving a physical database created in Microsoft SQL server 2008R2, that makes use of advanced features like Full-Text Search on varchar(MAX) FILESTREAM BLOBS to SQL Azure?

What architectural changes are needed? What steps are involved in this transition? SQL Azure Migration Wizzard can't simply be the magic tool that automagicly makes the transition.
What are the compatibility issues that the migration tool can not fix and should be worked out "manually"?


texts: Insert into Table ignoring duplicate values

texts:
I am having a brain fart on figuring this out.  I have the following two Tables:
Table: parts
    part_id          INT IDENTITY(1,1) NOT NULL,
    part_number      VARCHAR(50) UNIQUE NOT NULL,
    part_description VARCHAR(MAX) NOT NULL,
    information      VARCHAR(MAX) NULL,
    manufacturer_id  INT NOT NULL,
    subcategory_id   INT NOT NULL

Table: part_temp
    part_num  VARCHAR(50) NOT NULL,
    part_desc VARCHAR(MAX) NULL,
    info      VARCHAR(MAX) NULL,
    man_id    INT NULL,
    sub_id    INT NULL

part_temp is my temporary table that contains data from a CSV file, that is why only one column is set to be NOT NULL.  I need to insert the data from part_temp into parts.
I have properly cleaned the data in the table so there are no null values trying to be inserted into rows which require a value.  My issue however is with my UNIQUE constraint for my part_number columns in the parts table.  There are duplicate values within the part_temp table so I need a way to be able to skip over them during my insert.  This is what I have tried so far, but it does not work:
INSERT INTO parts
SELECT DISTINCT pt.part_num, pt.part_desc, pt.info, m.manufacturer_id, s.subcategory_id
  FROM part_temp                AS pt
       FULL OUTER JOIN man_temp AS mt ON pt.man_id = mt.man_id
       INNER JOIN manufacturers AS m  ON mt.man_name = m.manufacturer_name
       FULL OUTER JOIN cat_temp AS ct ON pt.sub_id = ct.category_id
       INNER JOIN subcategories AS s  ON ct.category_name = s.subcategory_name
 WHERE NOT EXISTS(SELECT part_number FROM parts WHERE part_number = pt.part_num)

These are the tables included in the joins not listed above
Table: man_temp
    man_id INT NOT NULL,
    man_name VARCHAR(100) NOT NULL

Table: manufacturers
    manufacturer_id   INT IDENTITY(1,1) NOT NULL,
    manufacturer_name VARCHAR(100) NOT NULL

Table: cat_temp
    category_id   INT NOT NULL,
    category_name VARCHAR(100) NOT NULL

Table: subcategories
    subcategory_id   INT IDENTITY(1,1) NOT NULL,
    subcategory_name VARCHAR(100) NOT NULL

What is it that is wrong with my INSERT query?
The specific error I am getting is:

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint.  Cannot insert duplicate key in object 'dbo.parts'.  The duplicate key value is (31335A11)

part_num 31335A11 appears in the csv file more than once.  As such it appears in the part_temp table more than once.  It would be easy if it was just this entry, but I have more than 1,000 repeat entries, so it would take forever to remove all the duplicates.  Nothing exists in parts as it is a brand new empty table I am trying to put values into.


texts: Why did MySQL remove the query cache feature after version 8.0?


texts: Add AG Database Add process results in Connectivity Fail

texts:
Recently I was asked to add a new DB to existing AG group.
Will adding a new AG database create any outage on Application . Especially ongoing In progress App side Sync processes with Server. Any Connection string Outages expexted ?

Sql Server 2014 SQL Server .


texts:
I am somewhat new to Access and I need help with a query that Access does not execute because of Not enough space on temporary disk.

I have one table. A product may have up to 3 IDs and a place to sell. If there is a place to sell, the name will be shown along with a combined identifier consisting of the three IDs plus the "k1" prefix. An example of the resulting combined identifier would be "k1 3232 232 1231233" (if it has three IDs).

The column names are ProductID1, ProductID2, ProductID3, Place1.

This is the query:

SELECT DISTINCT IIf(IsNull([Tam_Liste]![Place1]),"","k1 " & [Tam_Liste]![ProductID1] & " " & [Tam_Liste]![ProductID2] & " " & [Tam_Liste]![ProductID3]) AS item_sku, Tam_Liste.[Place1]
FROM Tam_Liste;


texts: limiting number of error log dump files retained

texts:
SQL server 2008 R2

if I limit the number of error logs retained by setting the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

will this limit the number of dump files retained as well?


texts: Proper way to store a value that could be multiple different types

texts:
I have an Answers table and a Questions table.

The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what its intended answer value type should be.

It will be important to parse these Answer values at one point or another since the numbers, at least, will need to be compared.

For a little more context, the questions and potential answers (typically a data type allowed for a textbox type input) are supplied by some users in a survey of sorts. The answers are then supplied by other specified users.

A couple options I've considered are:

A. XML or string that gets parsed differently depending on the intended type (which is kept track of in the question)

B. Three separate tables that reference (or are referenced by) the Answer table and are joined to based on the intended type. In this case, I'm not sure of the best way to set up the constraints to ensure each question has only one answer, or if that should be left to the application.

C. Three separate columns on the Answer table that can be retrieved based on the intended type.

I'd be happy just to get some input on the pros and cons of these approaches, or alternate approaches I hadn't considered.


texts: Most efficient way to do many UPDATES in Postgresql

texts:
I have to update cca 2.5 milion rows in a table with cca 20 milion rows.

The updated column value is based on value of another column, but another select is required to determine it's value. The column being updated is also a part of an index.

Currently I generated sql script with one UPDATE statement per row, but I imagine this is not very efficient. What's the most efficient way and are there any post update actions to perform to keep the table performing well? The table is being constantly used (selected/updated/inserted to), I don't want this to cause any downtime. The version of postgresql is 9.1.

EDIT

The updates in the script are of form

UPDATE table1 SET fid=123 WHERE id=345;

Where id column is integer and the primary key of the table.

The schema looks like this:

CREATE TABLE table1 (
    id serial PRIMARY KEY,
    date timestamp,
    rname varchar(50),
    fid integer references table2(id)
);

CREATE INDEX ON table1 (fid, date);

CREATE TABLE table2 (
    id serial PRIMARY KEY,
    rname varchar(50)
);

The fid column was recently added and I need to update some records in table1 so the records are properly linked by record id and not a rname column which causes data redundancy and other problems.

I've written perl script that generates set of update statements to do so based on the current state of the tables. The new inserted records in table have fid properly filled, I need to update the old.


texts:
I have a database with several hundred fields but my data structure is wrong. It is currently in uk format as follows:

d/m/y 01/01/85 01/01/96 23/12/87

What would be the most efficient way to change the dates in bulk to sql standard of year/month/day

eg. 02/01/85 --> 1985/01/02


texts: What all are the recoverable points of failure in an Oracle database?

texts:
I am testing a product against an Oracle database for failure scenarios. The product talks to Oracle through a session and maintains a few tables. I want to make sure that whenever Oracle recovers from whatever errors it can recover, the product can recover too.

For this, I will be manually simulating failures and checking what happens. What are all the failure scenarios I should try?


texts: The process is running and is waiting for a response from the server - transactional replication

texts:
I could not yet figure it out why one of my transaction replication subscription is constantly coming out with the following annoying message:

The process is running and is waiting for a response from the server.
Query Timeout Expired.

I have tried to increase the timeout on the Log Reader Agent Profile, but it is read-only.
I have also tried to create another profile and associate it with the Log Reader Agent, but it is not applied.

Questions:

1) How can I change the Log Reader Agent Profile via T-SQL? That would give me the proper error message.

2) How can I find out what is causing the timeout, where is it stuck?

From my previous experience, this error message disappears with time, but in this case it is not going away, and I feel that I need to change some setting, but I would like first to see clear where it is stuck, in order to assess best what exactly to change and why.


texts:
I am building a website on  MySQL with php, and I have 400+ products in a table. I have three columns with comma separated values, each has 1-3 values separated by commas in each row.
I was wondering if my db would be faster if I make separate tables for those three columns?


texts: How to create a flexible table schema for storing messages from different chats?

texts:
Help please solve the following situation:

There are two kinds of API where message history is stored, it's Zopim and Chat2Desc (import into Postman). While these two but can then others appear.

And my database with the users table:

Table users
id , email, phone, ...

In Zopim, users are identified via email, and in Chat2Desc through the phone. For me, these two fields are important, whatever the chat was and how many would not be.

That is, if I receive an email or a user's phone in messages, I make a request to my database (table users) to identify my user.

And in principle, even the structure of chat rooms is not important.I'll somehow choose them.And here's how to properly save them, so much so that I had one structure for everyone.

And that's what I came up with (Something I don't like, especially the chat_clients table):

Explanation:

Table chats (Data for chat):

client_id   - indicates the id of the chat_clients table
duration    - the duration of the chat (120 seconds)
system_type - stores the name of the chat (Zopim, Chat2Desc, ...)
created_at  - creation date

Table  chat_clients (information about users who were in the chat):

is_agent      - 0 | 1: 1 => my user, 0 => not my
user_id       - is the user id. Contains either id from the users table or empty.
assigned_data - those initials under which users were in the chat
bean_module   - it does not matter (information about my user)
unique_col    - There will either be a email (from Zopim) or a phone (from Chat2Desc, Or I think to store the id of the users table). Will guarantee the uniqueness of the values.

The users_id + unique_col bunch is unique (UNIQUE KEY user_id_unique_col_UQ (user_id, unique_col))

Table chat_messages:

text       - the text of the message.
client_id  - indicates the id of the chat_clients table
chat_id    - indicates the id of the table chats
file_id    - indicates the id of the chat_files table
transport  - the value will be for Chat2Desc (Viber, WhatsApp, ...), for Zopim, so it's not empty, Zopim

Table chat_files Information about the transferred files in the chat. Analogical tables may be not to store additional information.

In the future I'm going to select the history of correspondence for each user.

Q: How can I improve the structure of tables to get more flexibility ?

Thank you in advance.


texts:
When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database?

BACKUP DATABASE ExampleDatabase To DISK = N'...'
WITH FORMAT,
MEDIANAME = N'ExampleDatabase-Media-yyy-mm-dd-hh-mm',
NAME = 'ExampleDatabase-yyy-mm-dd-hh-mm',
COPY_ONLY
...

will this result in only the Media Set with name ='ExampleDatabase-Media-yyy-mm-dd-hh-mm' being wiped (if it exists)?

The context is that I am doing some ad-hoc backup and restores from one server to another. And I want to make sure that the FORMAT in my command will not format any other Media Sets that might have been created.

I want to make sure that my backup statement that uses FORMAT will not interfere with any other Backup maintenance plans that are in place by destroying Media Sets other than the one I define in my statement.


texts: MySql Federated table and changes propagation

texts:
i have 10 mysql servers and one central mysql server that's keeping track of some numerical indexes. Idea is that each time one of those 10 servers request and id, that id is marked as used and no other server can use it. For that reason i have implemented a Federated table system which works almost perfectly for me - each server is writing changes into local table and it's automatically reflected on the table of the main server. Like that each of those 10 servers can see what's the next available id without even knowing about each other.

Now, the problem is that on the central server, to Federated, "target" table i have added one additional column. It turns out i have to replicate that same change on all those 10 servers manually! My expectation was that once i add new column on "federated target" table, all dependent tables on those 10 servers will get updated automatically to match their "parent" structure like they did once created for the first time.

Is there a way to enforce this in MySql? Basically i wanna have 1 table and structural as well as data changes of that table to be reflected automatically on all those 10 servers.


texts: What is the etymology of the hstore datatype in postgres?

texts: Where did the datatype get its name? Hash store?


texts: SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

texts:
I am using SSDT to create a database containing CLR user defined aggregates.
The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a post-deployment script to move the objects after deployment.
What I am attempting to do is:

Create the CLR aggregates in a single schema (the "CLR" schema) using the "Default schema" option
Use a CREATE AGGREGATE wrapper in the desired schema to call the CLR aggregate

For example, I have a CLR aggregate like:
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]
public struct StatsEntropy : IBinarySerialize
{
 ... etc
}

This get compiled and becomes CLR.StatsEntropy in the database. The database is called AnalysisFunctions, so it has an assembly also called AnalysisFunctions. This is no problem.
I then create a wrapper in the Stats schema like so:
CREATE AGGREGATE [Stats].[Entropy]
(
    @Values FLOAT
)
RETURNS FLOAT
EXTERNAL NAME AnalysisFunctions.StatsEntropy

This works without error when I build via debug. I have my debug connection set to a named instance of SQL Server. If I press F5, it compiles and deploys without any problem. The code is now usable on the server, including the wrapper.
But, if I attempt to publish to a server using Publish, it does not work. Instead it fails with:

Errors occurred while modeling the target database. Deployment cannot continue.

There are no further error messages either in the Data Tools Operations panel or error list. ssdttrace shows no useful information:

This only happens when the database already exists on the server. If I drop the database, it can deploy successfully. I can deploy successfully via debug even if the database already exists, it is only via Publish that the problem occurs. I did not start getting this problem until I added the aggregate wrappers.
Previously I was also getting errors indicating that the assembly AnalysisFunctions had an unresolved reference due to the use of EXTERNAL NAME - again working via debug, not via publish. But now that error has mysteriously vanished.
I am using Visual Studio 2017 15.9.21 and SSDT 15.1.62002.01090, deploying to SQL Server 2019.
Does anyone have any idea what this error might be, or how I can debug it?


texts: Does adding non-clustered indexes to Transactional Replication increase latency

texts:
I would like to add non-clustered indexes to my Publication for Transactional Replication. I am aware that replication works by reading the transaction log for the published database and sends the updates to the subscribers. My question is: does transaction log include updates to indexes and therefore would this double the updates sent to the subscriber? Will it send updates to subscriber when I run Index maintenance ie: Rebuilding/Reorganizing indexes?

If so, then perhaps creating non-clustered indexes as part of post snapshot script application is a better way. If not, then I prefer to keep it as part of Publication.


texts:
Let's say I have a notifications list, and these notifications MAY implicitly reference a row from table B, or table C or etc (so that selecting that nofication in a client app would result in fetching a different result).

The ad-hoc solution was to create a notifications table and add nullable columns for every possible reference type, which feels wrong.

Another ad-hoc solution returns the same table with nullables during query, but leaves the notifications tables clean.

DECLARE @tmpF TABLE(id INT)
DECLARE @tmpA TABLE(two CHAR(1), f_id INT)
DECLARE @tmpB TABLE(three DECIMAL(5, 2), f_id INT)

-- 6 bases
INSERT INTO @tmpF (id) VALUES (1), (2), (3), (4), (5), (6)
-- 3 A-s
INSERT INTO @tmpA (f_id, two) VALUES (2, 'a'), (3, 'b'), (4, 'c')
-- 2 B-s (a base can live by itself)
INSERT INTO @tmpB (f_id, three) VALUES (5, 3.14), (1, 2.17), (5, 5.123)

-- Results in the same table with nullable columns
SELECT f.id, two, three
FROM @tmpF AS f
  LEFT JOIN @tmpA a ON f.id = a.f_id
  LEFT JOIN @tmpB b ON f.id = b.f_id

What would be some better strategy to model it? Or should try and not model this at all? Like having the client parse some notifications content  to find what should load (which is making up some parsing code, so hence being interesting in a database solution)


texts: Changing default permenant USERS tablespace to bigfile

texts: I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile.   How can I either install brand new database with users being bigfile or changing the existing USERS tablespace to bigfile?


texts: Modify MySQL foreign key related column without dropping foreign key relation

texts:
I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I dropped the foreign key relation, altered the column and created foreign key relation.

Is there any way to alter foreign key related columns without dropping the relation?


texts: Elegant way of sending e-mails with Service Broker

texts:
Is there an elegant way for sending e-mails using a Service Broker service/queue?

I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on the queue.

According to the book SQL Server 2005 DBA Street Smarts: A Real World Guide to SQL Server 2005 Certification Skills, Database Mail uses the Service Broker:

  Additionaly, Database Mail is scalable because it uses the Service Broker queue, which allows the requests to be handled asynchronously and even saves the request if the server goes down before it can be handled.

Maybe I'm too off base here, but, is there a way I can send messages directly to the Database Mail queue using Service Broker?

If I have to set up a stored procedure for sending an e-mail on behalf of Service Broker, I think it should be easier to just setup a job on the SQL Server Agent to check for the events I'm interested in inside the error log and send the operators an e-mail.

I'm open to suggestions.


texts: MongoDB shell version error (Server has startup warnings)

texts:
I installed MongoDB under Ubuntu server and when inter to MongoDB shell i got this error and warnings.

MongoDB shell version: 2.4.7
connecting to: test
Server has startup warnings:
Sun Oct 27 09:58:39.360 [initandlisten]
Sun Oct 27 09:58:39.360 [initandlisten] ** NOTE: This is a 32 bit MongoDB binary.
Sun Oct 27 09:58:39.360 [initandlisten] **       32 bit builds are limited to less than 2GB of data (or less with --journal).
Sun Oct 27 09:58:39.360 [initandlisten] **       Note that journaling defaults to off for 32 bit and is currently off.
Sun Oct 27 09:58:39.360 [initandlisten] **       See http://dochub.mongodb.org/core/32bit
Sun Oct 27 09:58:39.360 [initandlisten]

how I hide this warnings
Thanks


texts: LOAD DATA INFILE fatal error encountered

texts:
I use this query for loading sql data from my pc to localhost mysql database.(version 5.0.10 working witj XAMPP)

The query is as below:

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' IGNORE INTO TABLE " + tblname + " CHARACTER SET UTF8", conn);

And I tried this also (enclosed tblname)

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' INTO TABLE `" + tblname + "` CHARACTER SET UTF8", conn);

And one line of my sql data (loaded without problem by phpmyadmin) :

INSERT INTO `atelier` VALUES(1,'Chateau Carbonnieux -1','26.12.2013 00:00:00',23,10,0,'4 macarons differents','mamie','15.12.2013 11:09:14','sabrina','18.12.2013 05:29:26');

The variable filename is : "E:\Macdata\20131228\atelier.sql" (with double backslah)

And the tblname is the table name read directly form database is: "atelier".

When the program reaches the line  cmd.ExecuteNonQuery() 30 seconds after i get "Error 0 fatal error ecountered attempting to read the resultset."

This data file is loaded without problem with phpmyadmin.

What can be the reason ? MySql version ? Any help ?

As I'am a newbie in mysql, i used this link for creating my query command. Often they send this link for answer. But in this link there is no remarq to enclose the filename with ' otherwise it does not work it gives an error. http://dev.mysql.com/doc/refman/5.1/en/load-data.html


texts:
I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options:

DELETE QUICK
To set some table level logging off so that mysql doesn't put data
in rollback segments and that could make it faster as it happens
in oracle.

Please throw some light on it while keeping in mind that I can't use TRUNCATE.


texts: Can @parameter IS NULL slow down the execution of query?

texts:
Option 1:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge

Option 2:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

IF @MinimumAge IS NULL
BEGIN
    select * from Students s
END
ELSE
BEGIN
    select * from Students s where s.Age >= @MinimumAge
END

Will option 1 be slower than option 2 because it has extra WHERE clause? Or will SQL Server take care of that ?

I think option 1 is good as I don't have to duplicate the code, unless it's slower. The original procedure has many lines of code. So I don't want to duplicate all code just for one condition, unless it's the only good option.

There is indexing on the age column and it does not allow nulls.

The problem is, Actual SP is having many lines of code - So I don't want to duplicate all code just to add one where condition, unless it's the only good option.


texts: testing an extraneous attribute in a relation using functional dependency

texts:
I am studying the book "Database System Concepts" by sudarshan . In chapter 8.4.3

in a dependency alpha->beta , If A ∈ aplha to check if A is extraneous,
let (gamma = alpha − { A}), and check if (gamma → beta) can
be inferred from F . To do so, compute gamma+(the closure of gamma ) under F
if gamma+ includes all attributes in beta, then A is extraneous in alpha

Now my problem is , will I have to include alpha-> beta too while making the closure under F. It doesn't say to compute the closure under F-{aplha->beta} rather under F. It seems counter intuitive.

But during an extraneous attribute in A ∈ beta , we indeed calculate closure of alpha+ under (F-{alpha-beta} ∪ aplha->{beta-A})