texts: Delete a variable number of records by a chronological order

texts:
Our developers have been using a cursor within a stored procedure to delete old password history.  The number of records to delete is being passed by variable.

DECLARE hist_cursor CURSOR LOCAL FOR
    SELECT history_nr   FROM usr_pwd_hist   WHERE usr_id = @usr_id
    ORDER BY    history_nr ASC

OPEN hist_cursor

WHILE @to_delete > 0
BEGIN
    FETCH NEXT FROM hist_cursor INTO @hist_val

    DELETE FROM usr_pwd_hist WHERE CURRENT OF hist_cursor

    SET @to_delete = @to_delete-1
END

CLOSE hist_cursor;
DEALLOCATE hist_cursor;

I would like to replace this with a set based approach.  I can't do a simple top statement because the number of records to delete is a variable.  I can't use a top with a variable without dynamic sql and by policy we don't allow dynamic sql in production.

I'm considering this approach below but it makes me nervous as I know that Microsoft is planning on changing the way ROWCOUNT affects return results.  By putting the delete targets in a subquery I should be ok with future SQL versions, but I'm still wondering if there is a better way to delete a variable number of records by a chronological order.

SET ROWCOUNT @to_delete;  /* limit records to be deleted */
DELETE FROM usr_pwd_hist WHERE history_nr IN
(
    SELECT history_nr
    FROM usr_pwd_hist
    WHERE usr_id = @usr_id
    ORDER BY history_nr ASC
);
SET ROWCOUNT 0;  /* return rowcount to default setting */


texts: Possibility of fixing corrupt MySQL database

texts:
Obviously this wouldn't be an issue if there was a backup, but this database started out as just a test.

Soon it started to be used for more important data and then I made a plan to move it to a production environment and set up some type of regular backups.

Currently I have the prod environment set up and was planning on making the move today. But (as luck would have it) when I tried to get into the server where the DB lives I found I had no connectivity. I opened up a console and saw that there were disk errors. I tried to fix these with fsck but now, although it boots, I can't login and I see all types of errors during boot.

So as a last resort I tried to use an ubuntu live cd to get in and copy some files to another destination.

I noticed when I copy the files in the MySQL datadir, it can't copy the ibdata1 or ib_logfile1. I get the errors:

cp: error reading 'ibdata1': Input/output error
cp: error reading 'ib_logfile1': Input/output error

So before I write this off as a loss (and gain valuable experience with the need for backups) I figured I'd check here to see if there is any way to recover the MySQL data.


texts: Getting SQL server to recognise a date column

texts:
I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed.

My table looks like this

orders (
order_id INT PRIMARY KEY
, user_id INT
, date_created DATE
, order_value FLOAT
, city_id INT
)

I have used this code to get todays date in the table -

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders

This works fine but when I try attempt a where clause below this

Select *,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) as Today
From orders
where Today -1 = date_created;

I receive this error "Invalid column name 'Today'"
So my question is simply how can I get this table to recognise the the date I added as a new column and allow me to perform work on it?

Thanks in advance


texts: cross-database and cross-Availability Group queries and updates: which node do they go to with ApplicationIntent?

texts:
This is a simplified view of the scenario we are considering:

Node1
  AG1
    Database A (master readwrite)
  AG2
    Database B (secondary readonly)
Node2
  AG1
    Database A (secondary readonly)
  AG2
    Database B (master readwrite)

This is for SQL Server 2016 SP2+. I believe the behavior regarding cross-AG
transactions is essentially the same in later editions, though in later editions
the DTC is always used.

Now suppose a client application connects to the AG2 listener with ApplicationIntent=ReadOnly and Database=B.
Then presumably they are connected to Node1.
Now suppose that the app does a select ... join between database A and B. Is Node1
(readwrite for AG1) used for the database A data, or Node2?
Similarly, suppose the client executes some update SQL against database A
on that "readonly" connection; will this succeed (since database A is readwrite
on that node) or not?

Also I presume the behavior is identical in this respect if the client app
just connects directly to Node1 without using the listener.

Is there any way in the sql itself to namespace the reference to a db to
force it to be from another node?

Are there additional performance costs to cross-AG transactions (in addition
to whether they are entirely serviced in the same node or not)?

Does it matter if what is actually done from the client app is to exec
a stored procedure (which might even be in another database C), and then
the body of that SP does cross-AG operations?

Thanks....


texts: Best storage engine for huge tables with mostly update operations (74 million records, MyISAM table size 7.6G)

texts:
What will be the best storage engine for table with 74 million records, currently stored as MyISAM table size 7.6G.

Operations are mostly select and update. DDL operations (ALTER ADD COLUMN) are occasionally performed.

Joins and alter operations on MyISAM tables are extremely slow.

Will  InnoDB with large innodb_buffer_pool_size be optimal for this scenario or shall I go with In-memory tables?


texts: Is it possible to update all spatial index extents in one query?

texts:
I work for a county government and probably 95% of the data in the GIS database (running on Microsoft SQL Server 2014) is within the county area.

I've noticed that in some of the data the extent of the spatial indices is too large and in a few cases too small, which is causing queries to run slower than they should.

Question

Is there a way I can run down all the spatial indices and update the extent? I have the X/Y min/max for the minimum bounding rectangle of the county plus a 2 mile buffer already to plug in.

In ArcGIS there is a function to rebuild a spatial index but it doesn't alter the extent of the index. I see in the docs that I have to create a new index with the updated properties with the option of dropping the old index that has the same name. Not sure how to code this out.


texts: SSL connection to AWS RDS Postgresql instance from windows client

texts:
I am trying to connect to a PG instance hosted on AWS RDS using a secure SSL connection. Where do I store the public key on windows so that it is automatically applied to the connection?

Please note that when I open PGAdmin it only lets me specify .crt and .key files. Is there a way to point it to .pem files?

I downloaded the public key referenced here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

On Linux it is a simple matter of putting this key into the default OpenSSL directory and postgres seems to detect it and take care of the rest itself.

On Windows I have not figured out how to do this or where to store the .pem file or how to point PGAdmin to a .pem file.


texts: Do postgres autovacuum properties persist for DB replications?

texts: I would like to understand postgres replication options more, and as I am doing my research, I have come across a concept of autovacuuming in efforts to create routine DB maintenance.  My question is whether or not autovacuum settings persist across replications? My initial thought is no, because some of these parameters are set at a server level? But also there are table and DB specific autovacuum options.  If I set certain autovacuum properties on a specific postgres table or DB, would these properties translate across replicas of the DBs? Thanks in advance for the help.


texts: Why provide database name when starting postgresql base backup

texts:
According to the postgresql 8 manual, before starting a base backup (i.e. copying the files out of the data directory), you need to issue a command like this:

psql -d DATABASENAME -c "SELECT pg_start_backup('label');

But, since the postgresql files in the data directory contain ALL databases held by postgresql, what is the point of specifying DATABASENAME?  (eg: mydb).  Is it equally effecting to just use template0 as the databasename?


texts: Query that only uses index takes long time to execute

texts:
I have a table with ~69,000 rows. I have this very simple query:

-- SET STATISTICS TIME ON
SELECT ID, FirstName, LastName FROM Customers
-- SQL Server Execution Times: CPU time = 31 ms, elapsed time = 901 ms.

I also have this non clustered index:

CREATE NONCLUSTERED INDEX IX1 ON Customers (LastName ASC, FirstName ASC, ID ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON PRIMARY

The execution plan indicates that this index is being used. Yet the query takes ~1 second to return 69k rows which seems odd to me. I am wondering if 1 second is an acceptable amount of time or is it possible to improve this query.

Edit 1:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
   Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

It runs on a dedicated Dell PowerEdge server with 16GB RAM and two SCSI hard drives.

Edit 2:

I get almost similar timings (~1 second) even if I run the query on the server itself using named pipes/shared memory/TCP connections.


texts: Choosing the correct database type for single type of entry

texts:
So I'm building an application which constantly gets data from an API, basically the API outputs data through JSON which shows a certain speed for different hosts. What I want to here is query the app every minute and store this data within a database.

The idea is that the app will use the data to build graphs. These graphs will show the collective speeds of all hosts and for every host seperately. Hosts are assigned to a user

1 user has multiple  hosts with each a certain speed. Now considering the data that will be stored will grow over time and the relations between the entities is rather limited I was wondering if I should look at a NoSQL database like MongoDB or DynamoDB.


texts: Which authentication is better for MS-SQL Server 2019 : integrated security=SSPI vs uid=sa;pwd=xx | windows auth vs sa auth

texts:
In my different web applications I use both ways, however, I would like to learn which method is better:
1: server=localhost;database=x; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes;

2: server=localhost;database=x;uid=sa;pwd=y;

In my web application, for each database query, I open a connection and then close the connection.
So I am pretty much using the regular style such as:
    using (SqlConnection connection = new SqlConnection(srConnectionString))
    {
        connection.Open();
        using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
        {
            DA.Fill(dSet);
        }
    }

You can safely assume that there are hundreds or even thousands of queries at any given second.
My questions:
Are there any performance wise difference between 2 authentication methodology?
Are there any security difference between 2 authentication methodology?
I don't allow remote connections to the SQL Server. So only local connections are allowed.
Operating system Windows Server 2019


texts: What ISN'T included in SQL Server snapshot replication?

texts:
When creating a publication in SQL Server 2012 I get the options to include articles of type:

tables
views
stored procedures
UDFs

Since this list didn't include user-defined types, I was expecting those NOT to be replicated. But happily they were! (i.e. user-defined table types were replicated)

So question: What CANNOT be replicated in SQL Server? Also, why WERE the types replicated? Do they count as 'tables'?


texts:
Is it possible to compress a backup file to .zip (or another) in a stored procedure invoked from a maintenance plan?

My maintenance plan contains these steps:

check DB (consistency, re_index, maybe not important)
full backup
to check backup file by using restore_verifyonly
shrink database

After restore_verifyonly/shrink database I want to compress this file.


texts: Construct an SQL query to find the names of each department with more than 5 professors

texts:
Schema

Student         (id integer, name string, address string, status string)
Course        (crsCode string, deptID string, crsName string, descr string)
Transcript     (studentID integer, crsCode string, semester string, numericGrade double)
Teaching        (profID integer, crsCode string, semester string)
Department    (deptID string, name string, budget integer)
Professor        (id integer, name string, deptID string)

SELECT Department.name
FROM Department
JOIN Professor
ON Professor.deptID == Department.deptID
HAVING COUNT(Professor.id) > 5;

I don't know why, but for some reason this SQL query is not working. Please help me if you can.


texts: Postgres \copy command into single column without delimiter

texts:
I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide.

The problem is that this files has a lot of different characters and i don't know which to use as delimiter. In fact I don't need a delimiter at all as I am importing files in a single column table.


texts: Oracle: Inserting into table error - either duplicate column name or invalid datatype

texts:
I'm trying to insert data from an external table.
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)
SELECT *
    FROM EXTERNAL ((
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

But now i get this error:
Error at Command Line : 171 Column : 7
Error report -
SQL Error: ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"

Line 171 is the last line of this part
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
      (construct_id,
      n_term ,
      enz_name,
      c_term,
      cpp,
      mutations,
      mw_kda)

Which is clearly not a duplicate column.
If I do:
INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT *
    FROM EXTERNAL ((
      ext.construct_id NUMBER (10),
      ext.n_term VARCHAR2 (50),
      ext.enz_name VARCHAR2 (50),
      ext.c_term VARCHAR2 (50),
      ext.cpp VARCHAR2 (50),
      ext.mutations VARCHAR2 (50),
      ext.mw_kda NUMBER (7,3))

  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
    REJECT LIMIT UNLIMITED) ext

    WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
    );

I get
Error at Command Line : 174 Column : 10
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Line 171 is ext.construct_id NUMBER (10),


texts: pg_cron connection refused with postgres login

texts:
I am trying to run a function periodically with pg_cron but every time it wants to execute it throws connection refused. the cron job is created using the postgres user.
pg_hba.conf
local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
host    all             all             127.0.0.1/32            md5
host    postgres        postgres        localhost               trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I've tried adding a .pgpass file but, I don't know where to put it.
this is my machine version:
Debian GNU/Linux 10 (buster)


texts: Deadlock on insert for a table with an insert trigger that updates column in same triggered table

texts:
By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the customer that owns the resource (related via Resource -> Location -> Customer).

I have tried to set this 6 number using an after insert trigger. The logic works fine but causes deadlocks when I simulate many inserts coming in. I can eliminate this deadlock by adding a tablockx hint to the insert. However since Linq2SQL will be used to insert data I would like to avoid having to add a locking hint to the insert. I was hoping some combination of locking hints or index hints in the trigger might work instead.

The schema:

CREATE TABLE [Resource] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [nIdentifier]           INT NOT NULL DEFAULT 0,
    [fkLocation]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ([pkGUID] ASC)
)
CREATE TABLE [Location] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [fkCustomer]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([pkGUID]
)
CREATE TABLE [Customer] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([pkGUID]
)

Currently there are no indexes on the Resource table except the clustered primary key.

The trigger:

CREATE TRIGGER [dbo].[Trigger_Resource_Insert]
ON [dbo].[Resource]
FOR INSERT
AS
BEGIN
    SET NoCount ON

    ;with relevantCustomers as (
        select c.pkGUID
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join Customer c on l.fkCustomer = c.pkGUID
        group by c.pkGUID
    ), maxNumbers as (
        select rc.pkGUID, max = case when max(x.nIdentifier) < 100000 then 100000 else max(x.nIdentifier) end
        from relevantCustomers rc
        join Location l on l.fkCustomer = rc.pkGUID
        join Resource x on x.fkLocation = l.pkGUID
        group by rc.pkGUID
    ), numbers as (
        select i.pkGUID, number = row_number() over (partition by n.pkGUID order by i.pkGUID) + n.max
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join maxNumbers n on n.pkGUID = l.fkCustomer
    )
    update x
    set nIdentifier = n.number
    from Resource x
    join numbers n on x.pkGUID = n.pkGUID
END

An insert generating a bunch of data:

;with x as (
    select num = 1
    union all
    select x.num + 1
    from x
    where x.num < {0}
), l as (
    select top {1} fkLocation = pkGUID, sLocationName from Location order by sLocationName
)
insert into Resource with (tablockx) (pkGUID, fkLocation, ...)
select newid(), l.fkLocation, ...
from x
cross join l

Deadlock graph from SQL profiler when running inserts without tablockx hint:


texts: Handle concurrency when insertion depends on reading

texts:
[Short]

I have the following situation: user A attempts to insert data DA into the database. To check whether user A is allowed to insert DA, I need to run a query and do some computation. The problem I'm running into is that while I do the computation, another user (B) also attempts to insert data into the database. Now, suppose both users read the information needed for the computation before new data is inserted, then they might both get cleared for insertion whilst data from user A would forbid user B from inserting, thus leaving the database in a inconsistent state.

How can I solve this kind of concurrency in Azure SQL Database V12?

[Detailed]

The data the user is inserting is the beginning and end of a time interval, such as start: 6:00, end: 7:00.
The requirement is that there must be no time interval overlaps. This means that intervals start: 6:00, end: 9:00 and start: 5:00, end: 6:00 can't both exist.
Currently what I'm doing is checking whether there are any rows that overlap the new interval the user is trying to insert using the following query:

SELECT COUNT(*) FROM [Table1] WHERE Start <= attempEnd && End >= attemptStart

Now, the problem is that multiple users might be trying to insert an interval and these new intervals might overlap each other. However, this information might not be available at the time the query above runs, which causes overlapping intervals being inserted.

How can I solve this kind of concurrency in Azure SQL Database V12?


texts: Monitor executed SQL statements from external application

texts:
I have an external application connecting to a SQL Server 2008 Express database.

Is it possible to monitor (view) the SQL statements that are generated by the external application at all? I am trying to work out the tables (and the relevant joins) used in the queries.

Thanks


texts:
I run the following queries on a collection that have a lot of entries that contain [ and ].

 db.test.find({word:/[\[]/});           // could not be run in console
 db.test.find({word:/\[/ });            // could not be run in console
 db.test.find({word:/\]/});             //returns result
 db.test.find({word:/[\]]/});           //returns result
 db.test.find({word:{$regex:"\["}});    //no result
 db.test.find({word:{$regex:"\[[]"}});  //returns result

Why using \[ is such strange? Is it a bug?

I am using mongodb 2.2.0, perhaps


texts: AT TIME ZONE direction changes based on column having a timezone

texts:
Why does Postgres do the opposite thing for TS with and without Timezones?  In one case it adds hours, in the other case it subtracts.  Just to confuse things more, when specifying GMT+x, the hours are added in the case of TZ-naive and subtracted when the column is TZ-aware, but the opposite is done when using a time zone specified as 'Region/City'

Africa/Johannesburg is ahead of UTC by 2 hours, but when I select the column with AT TIME ZONE 'Africa/Johannesburg' it returns the time as the stored value MINUS 2 hours.

SELECT
   admission_at AT TIME ZONE 'gmt+2' AS gmt_plus_2,
   admission_at AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb,
   admission_at AS naieve
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;
       gmt_plus_2       |       africa_jnb       |        naieve
------------------------+------------------------+---------------------
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
(3 rows)

As a test I created a table with two columns, one for time with and one without timezone information.

SELECT
 time1,
 time1 AT TIME ZONE 'Africa/Johannesburg' AS naieve,
 time1 AT TIME ZONE 'gmt+2' AS t1_gmt2,
 time2, time2 AT TIME ZONE 'Africa/Johannesburg' with_tz,
 time2 AT TIME ZONE 'gmt+2' AS t2_gmt2
FROM times;

           time1            |            naieve             |            t1_gmt2            |             time2             |          with_tz           |          t2_gmt2
----------------------------+-------------------------------+-------------------------------+-------------------------------+----------------------------+----------------------------
 2018-06-14 08:47:35.382964 | 2018-06-14 06:47:35.382964+00 | 2018-06-14 10:47:35.382964+00 | 2018-06-14 08:47:35.382964+00 | 2018-06-14 10:47:35.382964 | 2018-06-14 06:47:35.382964
 2018-06-14 08:47:36.468022 | 2018-06-14 06:47:36.468022+00 | 2018-06-14 10:47:36.468022+00 | 2018-06-14 08:47:36.468022+00 | 2018-06-14 10:47:36.468022 | 2018-06-14 06:47:36.468022
 2018-06-14 08:47:37.342908 | 2018-06-14 06:47:37.342908+00 | 2018-06-14 10:47:37.342908+00 | 2018-06-14 08:47:37.342908+00 | 2018-06-14 10:47:37.342908 | 2018-06-14 06:47:37.342908
(3 rows)

The result is exactly the opposite of what I expect.  Why would a timestamp stored as naive convert correctly to 'GMT+2' by adding 2, but a TS with timezone do the opposite?

And why would it be the other way around when converting to 'Africa/Johannesburg', where the TZ-naive sees the value returned having 2 hours deducted while the TZ-aware value gets 2 hours added for the same value?

Note:  This is NOT a duplicate of "AT TIME ZONE" with zone name PostgreSQL bug? which relates to Time Zone abbreviations, which behaves differently from Time Zone Names.

In this instant regardless whether the name or the GMT+offset format is used, the result is reversed (time added vs time subtracted) depending on whether the column type is a TZ naive or TZ aware timestamp.


texts: How to name tables such as "Suppliers Categories"

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

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

So I am proposing these kind of changes:

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

The question is:

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

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

MySQL - Identifier-case-sensitivity
Larvel - Naming Conventions

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


texts: SQL Server replication - Could not add new article to publication

texts:
I have following script for replication that I am running in source database (subscription is not added yet, target database does not exist yet in target server):

use [myDatabase]
exec sp_replicationdboption @dbname = N'myDatabase', @optname = N'publish', @value = N'true'
GO

use [myDatabase]
exec sp_addpublication @publication = N'myDatabase_pub',
    @description = N'Transactional publication of database ''myDatabase'' from Publisher ''DATABASE-SERVER''.',
    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true',
    @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false',
    @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true',
    @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false',
    @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
    @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'myDatabase_pub', @frequency_type = 1, @frequency_interval = 0,
    @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0,
    @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
    @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0,
    @publisher_login = N'username', @publisher_password = N'password'
use [myDatabase]
exec sp_addarticle @publication = N'myDatabase_pub', @article = N'simple_Table', @source_owner = N'dbo',
    @source_object = N'simple_Table', @type = N'logbased', @description = null, @creation_script = null,
    @pre_creation_cmd = N'drop', @schema_option = 0x000000000803609F, @identityrangemanagementoption = N'manual',
    @destination_table = N'simple_Table', @destination_owner = N'dbo', @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dbosimple_Table', @del_cmd = N'CALL sp_MSdel_dbosimple_Table',
    @upd_cmd = N'SCALL sp_MSupd_dbosimple_Table'
GO

use [myDatabase]
exec sp_addarticle @publication = N'myDatabase_pub', @article = N'simple_Table_class', @source_owner = N'dbo',
    @source_object = N'simple_Table_class', @type = N'logbased', @description = null, @creation_script = null,
    @pre_creation_cmd = N'drop', @schema_option = 0x000000000803609F, @identityrangemanagementoption = N'manual',
    @destination_table = N'simple_Table_class', @destination_owner = N'dbo', @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dbosimple_Table_class',
    @del_cmd = N'CALL sp_MSdel_dbosimple_Table_class',
    @upd_cmd = N'SCALL sp_MSupd_dbosimple_Table_class'
GO

This script worked fine by few months. However today when I run it I got error like:

Could not add new article to publication 'myDatabase_pub' because of active schema change activities or a snapshot is being generated.

Do you know what how can I check what is blocking replication? What kind of schema changes?


texts:
Right now im accessing a SAMPLE db which is in remote server. I found that its default instance db2inst1 with database name "SAMPLE". Since everybody using the same database for all their purposes, i want to create a new DB for my application. i tried connecting to the server through telnet. Since there are lot of data in SAMPLE db, cloning or duplicating same would help me save time. Please can someone post some set of commands or steps to duplicate SAMPLE db through telnet and sqldeveloper. If not commands or steps for creating a new db also is fine.

FYI: DB2 version - v10.5.0.3


texts: Grouping rows by looking at two columns without considering the order and summing each separately

texts:
I have a table looks like this one below:

+---------+---------+-------+
| SOURCE  | DEST    | VALUE |
+---------+---------+-------+
| A       | B       | 1     |
| B       | A       | 2     |
| A       | B       | 3     |
| C       | D       | 5     |
| D       | C       | 6     |
+---------+---------+-------+

So what I am trying to do is grouping them together by looking at the value1 and value2 without considering the order of this two values, which mean "A and B" is the same as "B and A".

I am looking for a result looks like this:

+---------+---------+-------------+-----------+
|         |         | SUM(SOURCE) | SUM(DEST) |
+---------+---------+-------------+-----------+
| A       | B       | 4           | 2         |
| C       | D       | 5           | 6         |
+---------+---------+-------------+-----------+

Does anyone has any idea about how can I get this?

(I am trying to do this on both SQL Server, and MySQL)

Related: Grouping rows by looking at two columns without considering the order


texts: Mirror / replicate only 1 table of a database

texts:
I need a good way to replicate a single table in SQL Server 2012 between different servers and different databases. I tried replica which works just fine, but there might be a much better / easier way which I don't see right now.

Maybe there is a way to use the task to import data as a job. The data does not need to be synchronized all the time. It is fine that the second database [B] gets updated with the data from database [A] every 5-10 minutes.

Is it possible to setup a mirroring for only one table?

Of course I checked the web which gave me the idea to script the import task.

What else is possible and how?


texts: Missing Oracle Schema in SSMA Object Explorer

texts:
We are currently working on a project to move all of our disparate DBs (MySQL, Oracle, older versions of MS SQL) to SQL Server 2014. So far, pretty much everything has gone smoothly, however, there is one Oracle Database (10g 10.2.0.5), which we're trying to migrate using SSMA that just doesn't show up in the SSMA object explorer.

I have tried connecting to this database using the schema owner, the sys AND the system users and it just never shows up. ALL the other schemas on this instance show up and we can migrate them successfully.

Has anyone come across this issue before? Could anyone suggest possible reasons for this? I - and the SQL Server DBA - am stumped. In the interim, we're migrating subsets of data using smacked-together Perl scripts, but this is obviously not ideal.


texts: Is there an easy way to update a mysqldump file from version 9.11 to 10.11?

texts:
We have a client that has had their servers for soo long, they were still running MySQL 4.0.24.
Now that they are refreshing their servers, we took a mysqldump of their current database to put into the new servers and are getting errors like:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'call' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'call
(
path varchar(255) NOT NULL default '',
call_num int(12) unsigned ' at line 1

The section of the dump file it is complaining about is:

--
-- Table structure for table `call`
--

DROP TABLE IF EXISTS call;
CREATE TABLE call (
  path varchar(255) NOT NULL default '',
  call_num int(12) unsigned NOT NULL auto_increment,
  date datetime NOT NULL default '0000-00-00 00:00:00',
  idf varchar(4) NOT NULL default '404',
  location varchar(255) default NULL,
  who_req varchar(8) default NULL,
  who_res varchar(8) default NULL,
  PRIMARY KEY  (path,call_num)
) ENGINE=MyISAM;

--
-- Dumping data for table `call`
--

So, does anyone have any ideas on this one?


texts:
I'm trying to select data from information_schema but I'm getting the following error. How can I fix this?

mysql> SELECT * FROM information_schema.tables ;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 13)


texts:
Is there any way to get this query to return the index size as BYTES?

SELECT
    relid::regclass AS table,
    indexrelid::regclass AS index,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM
    pg_stat_user_indexes
    JOIN pg_index USING (indexrelid)
WHERE
    idx_scan = 0
    AND indisunique IS FALSE;

Currently it shows BYTES/KB/MB and GB...


texts: Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram

texts:
Situation
I am trying to figure out how to represent a one-to-one (1:1) relationship regarding a superclass/subclass or supertype-subtype structure in an entity-relationship diagram (ERD) of the kind created by means of MySQL Workbench (which, as you know, looks more close to a concrete SQL implementation than an original P. P. Chen diagram).
I am new to this, so I am not sure whether I have to type out all the attributes1 again in the subclass entities (or subtypes), i.e., MANAGER and DRIVER, or whether I there is a way of connecting to and grabbing the attributes from the superclass (or supertype) so that it is more clear that they relate, something like a FOREIGN KEY, I guess.
1. Each attribute in EMPLOYEE applies to MANAGER and DRIVER, but MANAGER and DRIVER will have some extra attributes that are not characteristics of the EMPLOYEE entity type (or table, once it is implemented).
Current diagram
Bellow is the ERD I have created so far:

Question
So, how can I represent accurately this sort of relationship in my diagram?


texts: What does select * from table where column * 'value'; do?

texts:
select * from table where column * 'value';

I accidentally wrote * for my where statement instead of =, and the query returned all rows in the table.

Made me wonder why an error wasn't thrown instead.

What does the * operator do in a where statement?


texts: Merging tables, but ignoring duplicate columns

texts:
I have two tables I want to merge:

city1(id, city, state, zip, lat, lon, county)

city2(id, city, state)

The tables contain overlapping city and state, but not primary keys. I can't figure out how to do this. UNION won't work, nor INSERT IGNORE due to the different column numbers and primary keys. So, I guess that leaves joins(left?), but I can't figure out the syntax to get it it excluding duplicates.

A NOTE

UNION will still display duplicate city/state combinations. From what I've read, UNION does prevent duplicates if the tables have different sizes.

Table Create Statements

Column and table names have changed

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_state_code` (`state_code`)
) ENGINE=InnoDB AUTO_INCREMENT=29739 DEFAULT CHARSET=latin1

CREATE TABLE `cities_extended` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  `zip` int(5) unsigned zerofill NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41756 DEFAULT CHARSET=latin1


texts: installing mysql 5.1 alongside mysql 5.5

texts: mysql 5.5 is installed. I need 5.1 to install an old website so it can be updated. The installer refuses to let me install onto the disk with 5.5. I don't have any other disks. I downloaded the package file DMG. Help? Can I get around this with a different kind of source file?


texts: Sum of different values on the same row in a table

texts:
I have a table with numbers from 0-5, under different field names (Score1, Score2 etc. all the way to Score50) that need to be summed at the last column of that same row. Each row represents a different client with its own info.

Is it possible and if it is, some help would be nice.


texts: What causes sql server to generate a new plan for a stored procedure?

texts:
We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc.  Unfortunately, we weren't able to capture the plan before and after to see what the bad plan looked like before we did a dbcc freeproccache, we also don't have query store set up.

My question is, what all can cause sql server to generate a new plan for a stored procedure?  Especially for an sp that is ran all of the time.  Some of the ones I know are:

dbcc freeproccache
rebooting the server
option recompile


texts: STATISTICS IO difference for heap and clustered index

texts:
Assume we have two tables with data:

create table heap (value int);
create table clust (value int primary key);
insert into heap values (1);
insert into clust values (1);

By inspecting their storage statistics

select obj.name, st.alloc_unit_type_desc, st.index_level, st.page_count
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    cross apply sys.dm_db_index_physical_stats(
        db_id(), ix.object_id, ix.index_id, NULL, 'DETAILED') st;

select obj.name, au.total_pages, au.used_pages, au.data_pages
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    join sys.partitions p on p.object_id = ix.object_id and p.index_id = ix.index_id
    join sys.allocation_units au on au.container_id = p.partition_id;

one can see that both occupy the same number of pages:

name  alloc_unit_type_desc index_level page_count
----- -------------------- ----------- ----------
heap  IN_ROW_DATA          0           1
clust IN_ROW_DATA          0           1

name  total_pages  used_pages  data_pages
----- ------------ ----------- -----------
heap  2            2           1
clust 2            2           1

In this case why statistics io

set nocount on;
set statistics io on;
declare @cnt int;
select @cnt = count(1) from heap;
select @cnt = count(1) from clust;
set statistics io off;

show that scanning the clustered index compared to scanning the heap takes one extra logical read?

Table 'heap'. Scan count 1, logical reads 1...
Table 'clust'. Scan count 1, logical reads 2...

What this extra logical read is?

P.S.
My question is not about "clustered index vs heap performance" or query tuning. I'm trying to better understand things involved into STATISTICS IO reported for clustered index scan.

The example is for SqlServer 2014

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

I also tried it on SqlServer 2008 R2

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

and result is the same (though one can not use sys.dm_db_index_physical_stats on 2008 R2 this way).


texts: Where can I find information on what resources are consumed when connections are established with MSSQL 2012?

texts:
I'm trying to troubleshoot the following intermittent sql connection error:

  "DESCRIPTION:    SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems."

This results in sql jobs failing, and our application getting the following exception:

  “A connection was successfully established with the server, but then an error occurred during the pre-login handshake”

A Jonathan Kehayias post to MSDN in 2011 indicates the problem is worker thread starvation and that I need to scale out with additional servers. We're using SQL Server 2012 Enterprise (version 11.0.5058.0) with a 4-processor, 64-bit server, with 64GB of RAM and max worker threads set to 512. As I type this we are under significant load and sys.dm_os_workers only shows 218 threads. There is a 2ms Threadpool signal waits, and the average Threadpool wait time is 5ms. There are also no errors in any of the SQL logs other than what I listed. Also, anecdotally speaking, the error is very sporadic, and it tends to happen more in the off hours when server load is relatively low. Because of this I'm not 100% convinced there is a worker thread starvation problem.

Instead I'm beginning to think there is a race condition that briefly exhausts resources needed to create a connection. In addition to our main application connecting to the database (via ADO), I have several monitoring and maintenance jobs running at regular intervals (some as short as 1 minute).  I learned from our developers that the defaults are used for connection pooling. There is nothing in the connection strings that regulate pool size or connection duration. It seems reasonable that when the connection pool needs to grow, and jobs are connecting to the database that either SQL or Windows resources become scarce, for lack of a better term, which results in the errors I listed.

I’m looking for any resources that go into detail on what happens when an attempt is made to establish a connection with SQL Server.  All I really know is that establishing a connection is expensive, which is why connection pooling is recommended. I’m actually kind of lost at the moment, and thought getting a better understanding of how sql connections are made would help me troubleshoot this error faster, but I haven’t been able to find much with Google.

Can someone help point me in the right direction?


texts: SQL Server database stuck in Recovery after Restoring .BAK to same instance

texts:
We've had an issue crop up a few times -- maybe we're not using 'best practices' to restore, but appreciate any advice. We run an IIS app with a SQL Server database, and sometimes we run two+ instances (multi-site) on the same server / SQL Server instance.

Let's say we have a database 'site1', and we create a site1.bak of that site. Then we need to set up another, so we restore site1.bak to a new database site2.

However, on restoring that database (and ensuring the file names are different), the ORIGINAL site says it's IN RECOVERY. The 'fix' is easy -- we run:

RESTORE DATABASE NameofDatabase
WITH RECOVERY

...but the fear is something else is going on that could be detrimental. Why would the original database go into restore when the .bak is being restored to a new database with different file names?


texts:
I have a database of size 840 GB which has 948 VLF's. On other hand there is one with size of 1.6 TB having 320 VLF's. Does that indicate the smaller database with that high count of VLF's is actually a problem? Also how can we decide that number to be too big or within OK range.

Please suggest


texts: Difference between Physical Reads and Read-Ahead Reads

texts:
I am trying to understand read-ahead reads, but it seems a bit complicated to me. I searched on the web and got the following:
From Reading Pages (Microsoft documentation):

Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query.

From an answer to Why is 'physical reads' less than 'read-ahead reads' & 'logical reads' in SQL Server for first time execution of query? by huntharo on Stack Overflow:

Physical Read - The query is blocked waiting for the page to be read from disk into the cache for immediate use.
Read-Ahead Read - The page is being read before it blocks the query and is read into the cache as are all reads. Read-Aheads are possible when you are scanning an index, in which case the next leaf pages in the index can assume to be needed and the read can be initiated for them before the query actually says it needs them. This allows the disk to be busy while the db engine is examining the contents of previously fetched pages.

Maybe someone could clarify the above using their own explanation because I can't find a detailed explanation for read-ahead reads.
To set an example, look at statistics io info:
Table 'TestLarge'. Scan count 1, logical reads 159185, physical reads 348, read-ahead reads 159209


texts:
I have a MongoDB user created into admin database

When i try to use some functions (from mongoDB native driver in nodeJs) it throws an error:

  { name: 'MongoError',
    message: 'not authorized on stop to execute command { findandmodify: "users", query: { _id: 1 }, new: true, remove: false, upsert: true, update: {...

{
    "_id" : "admin.admin",
    "user" : "admin",
    "db" : "admin",
    "credentials" : {
        "SCRAM-SHA-1" : {
            "iterationCount" : 10000,
            "salt" : "xxxx",
            "storedKey" : "xxxx",
            "serverKey" : "xxxx"
        }
    },
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        },
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readWriteAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "dbOwner",
            "db" : "admin"
        },
        {
            "role" : "clusterAdmin",
            "db" : "admin"
        }
    ]
}

Any other commands like find or update works.

I want this user to be a SUPER USER but I have assigned it all type of roles, but none of them works, i tried root, adminAnyDatabase, etc.

I'm using mongodb 3.0.9

Any idea how to solve this? Which permision do i need to allow findAndModify command?


texts: Bloated transaction log and .bak backup size confusion

texts:
I recently inherited a poorly-managed SQL server.

One of the databases is in rough shape. Here are the details:

The .mdf file is a modest 369MB.
The .ldf file is a bloated 29.7GB.
The recovery model is set to FULL.
The data file is backed up nightly without issue, and is 365MB.
The transaction log has NEVER been backed up.

I've restored the nightly backup to the same SQL server and poked around in it to make sure data looks to be intact. When I restore that nightly .bak backup file it recreates the .mdf and .ldf files at 369MB and 29.7GB, respectively.

Being decent with SQL, but not what I would call an expert by any means, I have a few follow up questions on this situation that I am hoping to get feedback on:

How is a 369MB .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?
Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.


texts: PostgreSQL : FATAL: could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or directory

texts:
I am new to PostgreSQL and stuck on an issue that PostgreSQL isn't starting up. It reported:

FATAL:  could not open log file "/path/to/log/filesystem/postgresql-Sun.log": No such file or directory
06:52:32.530 UTC [7440] LOG:  database system is shut down

I tried to find the solution online and changed the permission of the postgres user using chown, but that also didn't help or maybe I missed something.
Please advise.


texts: MySQL: A user cannot see insert to a table that happen after log in_

texts:
I have created a kind of primitive batch processor in MySQL, using scheduled events, which runs a stored procedure. Each time the procedure runs, it inserts a log message into a table. All this works fine, and when I log in as root, I can see the rows being added. However, when I log in as an ordinary user with:

gameiom_asia > show grants;
+--------------------------------------------------------------------------------+
| Grants for analytics@localhost                                                 |
+--------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'analytics'@'localhost' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `reporting`.* TO 'analytics'@'localhost'               |
+--------------------------------------------------------------------------------+
2 rows in set (0.25 sec)

I can only see what was already in the log table at that time, but newer inserts are not visible. I thought initially that it was because autocommit is off (but only for user sessions - it is in init_connect='SET autocommit=0'), so I added commit after all inserts and updates in the procedures, but this didn't have an effect.

Can anybody shed some light on this?


texts: Can SQL Server Replication decrease publisher performance?

texts:
We have an application that will generate a lot of insert/update transactions per second.
Recently, we've moved the application to a new server and since then, the performance deteriorated significantly (about 3x slower).  I know it could explained by many things but one of the main difference between the 2 servers is the SQL Server replication that was added to the new server.
My comprehension of the replication process is that the subscriber database will try to catch up with the publisher by using it's log file.  A slow latency would certainly affect the time required by the subscriber to synch back, but what about the publisher?  I don't know what are the exact replication configurations but is there scenarios where a publisher's performance would affected by the replication process?


texts: Join on nested obj attribute in JSONB column along and a varchar column

texts:
I have table connections where both columns are varchar:
+---------+---------------+
| user_id | other_user_id |
+=========+===============+
| foo     | bar           |
+---------+---------------+
| baz     | asdf          |
+---------+---------------+

And table events where user_id references connections.user_id:
+---------+----------------+
| user_id | payload        |
+=========+================+
| foo     | {              |
|         |   otherUser: { |
|         |     id: "bar"  |
|         |   }            |
|         | }              |
+---------+----------------+
| baz     | {              |
|         |   otherUser: { |
|         |     id: "asdf" |
|         |   }            |
|         | }              |
+---------+----------------+

I would like to JOIN the tables on connections.user_id = events.user_id and connections.other_user_id = events.payload.otherUser->>id
Though, I'm not sure how to access the id field in the nested otherUser object in the JSONB column.
This is the most I've come up with:
SELECT * FROM events ev
JOIN connections con ON con.user_id = ev.user_id AND con.other_user_id = ev.payload->otherUser->>id

also tried con.other_user_id = (ev.payload)::jsonb->otherUser->>id
for these ^ two options I get "column otheruser doesn't exist".
Then also tried:
con.other_user_id = text(jsonb_extract_path(ev.payload::jsonb, 'otherUser', 'id'))
Which throws no error but also no rows are returned.
But I get error about the syntax.


texts:
I have set up a table that logs every page view a visitor makes. This is going to result in a table with about 50,000 entries per week.

To keep things manageable and performing well I am intending on moving the content of the table to an archive each week or month.

I have a couple of questions, the first is how often would be sensible to move the contents?

Secondly what is the best way to move the contents?

This is a mysql database and the table has 11 columns mostly int or varchar averaging 80 characters.

When it comes to the archive should that be a series of tables or again split but maybe to a lesser degree?


texts: How can I get the MySQL super_read_only setting to work?

texts:
I can't seem to get the new super_read_only configuration option to work. Logged in as root or a user with SUPER privileges, I can still insert a row. If the user doesn't have super privileges, I get the correct error that the server is running with the -read-only option. Here is an example:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.01 sec)

mysql> insert into repl1 (value) values ('this should not work');
Query OK, 1 row affected (0.00 sec)

mysql> select * from repl1 where value like '%should not%';
+----+----------------------+
| id | value                |
+----+----------------------+
|  6 | this should not work |
+----+----------------------+
1 row in set (0.00 sec)


texts: Regressed queries after statistics update

texts:
I've scheduled job with statistics update on 100GB database (some of them were really old, from several months to 2-3 years), job ended succesfully after 20minutes at 1AM.
Now i checked Query Store (after 15 hours ~) and there are several regressed queries with markedly worse execution plans. Average times are significantly worse than one day before update stats.
What should i do now? Do you think DBCC FREEPROCCACHE could help? Thanks a lot
PS: SQL Server is version 2019, but database has Compatibility Level set on 100 (2008). Idk why, this is our new customer. This will be changed soon.


texts: Controlled shutdown/startup of a cluster

texts:
My three node SQL Server 2008 R2 physical server cluster needs to be shutdown, moved along with its storage to a new location, and restarted once in the new location. My desire is to have a clean, controlled shutdown/startup sequence and, if possible, for all SQL Server instances to remain on their original node during this process. So, if SQLInstA is on NodeA and SQLInstB is on NodeB and NodeC is empty, there is no failover during server shutdown and I’ll bring up the services individually upon startup.

I’ve read various recommendations regarding setting SQL Server offline, pausing nodes, stopping cluster services, setup startup types to manual.

My first thought was to set the SQLInstA and SQLInstB services offline in Failover Cluster Manager. Offline does not trigger a failover so they’ll stay put. But, rebooting will cause failover, regardless of my setting them to have only one preferred owner. The service doesn’t restart, even though I didn’t “disable auto start”, so even if both instances ended up on the same node, the services would be down on startup, which isn’t horrible.  Just manual failover work afterward.

I could stop the cluster services, but that triggers failover as well.  Is it important to stop cluster services in this case?

Any thoughts on how to accomplish this?


texts: What is the best way to find object dependancies in SQL Server

texts:
Possible Duplicate:
  How can I track database dependencies?
  How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?

I have been using the following SQL on SQL Server 2005 to find all the places where an object (table, stored procedure, view, function, etc) is used:

DECLARE @search_string NVARCHAR(max)
SET @search_string = 'foobar'
SELECT DISTINCT SCHEMA_NAME(so.[schema_id]) + '.' + so.[name] AS [Used By],
so.[type_desc] as [Type]
FROM [sys].[syscomments] sc
INNER JOIN [sys].[objects] so ON sc.[id] = so.[object_id]
WHERE sc.[text] like '%[~[. ]' + @search_string + '[~]. ]%' ESCAPE '~'
ORDER BY [Used By]

Are there any better ways of doing this?

Update, with advice from @JNK, I have changed it to use sql_modules rather than syscomments:

DECLARE @search_string NVARCHAR(max)
SET @search_string = 'eligibility'
SELECT DISTINCT SCHEMA_NAME(o.[schema_id]) + '.' + OBJECT_NAME(sm.[object_id]) AS [Used By],
o.[type_desc] AS [Type]
FROM [sys].[sql_modules] AS sm
INNER JOIN [sys].[objects] AS o ON sm.[object_id] = o.[object_id]
WHERE sm.[definition] LIKE '%[~[. ]' + @search_string + '[~]. ]%' ESCAPE '~'
ORDER BY [Used By]


texts:
Table students has about 10 million records; ID is indexed.
Student grades has 20 million records - student_id is indexed.
I am querying about 20,000 students by their ids ids with a join between students and parents:

select * from students s left join grades g on s.id=g.student_id
where (s.id IN (s1, s2... s1000)
or (s.id IN (s1001, s1002... s2000)
or (s.id IN (s.2001, s2002...s3000)
//until s20000)

I need to split the INs into multiple batches as IN can only get 1000 or less values.

The query takes about 5 minutes to return. Is there any way I can optimize it?

Thanks!


texts: Similar queries taking different times to complete

texts:
I had the following query:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
    a, b, c, d, e
  FROM (
    SELECT
      a, b, c, d, e,
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1)) X
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
  )
)
WHERE
  b LIKE ...
  AND e IS NULL
  AND adjust1>0
  AND (b NOT IN ('...','...','...'))
  OR  (b = '... AND c <> NULL)

I tried to change it to this:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
  FROM (
    SELECT
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1))
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
      AND b LIKE '..'
      AND e IS NULL
      AND (b NOT IN ('..','..','..'))
      OR  (b='..' AND c <> NULL)
  )
)
WHERE
  adjust1>0

My intention is to have all the filtering in the innermost query, and only give the outer ones the field X which is the one I have to use a lot. However, the original query takes a couple of seconds to execute, while the second one won't even finish.  I waited for almost 20 minutes and still no results.

Is there an obvious reason for this to happen that I might be overlooking?


texts: sql org chart hierarchical table to xml json

texts:
I have a table of employees and managers:

create table emp_mgr (emp varchar(99), mgr varchar(99))

insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr select 'Charles','Bob'; ; insert into emp_mgr select 'David','Bob'; insert into emp_mgr select 'Ethan','Bob'; insert into emp_mgr select 'Frank','Charles'; insert into emp_mgr select 'George','Charles'; insert into emp_mgr select 'Henry','Charles'; insert into emp_mgr select 'Ian','David'; insert into emp_mgr select 'James','Ethan'; insert into emp_mgr select 'Ken','Ethan'; insert into emp_mgr select 'Larry','Ethan'; insert into emp_mgr select 'Mike','Frank'; insert into emp_mgr select 'Ned','Frank'; insert into emp_mgr select 'Oliver','Frank'; insert into emp_mgr select 'Peter','Frank'; insert into emp_mgr select 'Quinn','George'; insert into emp_mgr select 'Robert','George'; insert into emp_mgr select 'Scott','George'; insert into emp_mgr select 'Ted','George'; insert into emp_mgr select 'Urban','George'; insert into emp_mgr select 'Victor','Henry'; insert into emp_mgr select 'Wes','Ian'; insert into emp_mgr select 'Xavier','James'; insert into emp_mgr select 'Young','James'; insert into emp_mgr select 'Zach','James';

and I want to generate this json file from it :

{ name:"Adam" , children:[   { name:"Bob" , children:[     { name:"Charles" , children:[       { name:"Frank" , children:[         { name:"Mike" },         { name:"Ned" },         { name:"Oliver" },         { name:"Peter" }       ]},       { name:"George" , children:[         { name:"Quinn" },         { name:"Robert" },         { name:"Scott" },         { name:"Ted" },         { name:"Urban" }       ]},       { name:"Henry" , children:[         { name:"Victor" }       ]}     ]},     { name:"David" , children:[       { name:"Ian" , children:[         { name:"Wes" }       ]}     ]},     { name:"Ethan" , children:[       { name:"James" , children:[         { name:"Xavier" },         { name:"Young" },         { name:"Zach" }       ]},       { name:"Ken" },       { name:"Larry" }     ]}   ]} ]}

Here's what I tried - With a recursive xml query..

CREATE function [dbo].[SelectChild](@name as varchar(99))
returns xml
begin
    return (
        select
            employee as "@name",
            dbo.SelectChild(employee)
        from emp_mgr
        where manager = @name
        for xml path('record'), elements
    )
end

select
    employee as "@name",
    dbo.SelectChild(employee)
from emp_mgr
where manager is null
for xml path ('record')

..I was able to generate this xml :

<record name="Adam">   <record name="Bob">     <record name="Charles">       <record name="Frank">         <record name="Mike" />         <record name="Ned" />         <record name="Oliver" />         <record name="Peter" />       </record>       <record name="George">         <record name="Quinn" />         <record name="Robert" />         <record name="Scott" />         <record name="Ted" />         <record name="Urban" />       </record>       <record name="Henry">         <record name="Victor" />       </record>     </record>     <record name="David">       <record name="Ian">         <record name="Wes" />       </record>     </record>     <record name="Ethan">       <record name="James">         <record name="Xavier" />         <record name="Young" />         <record name="Zach" />       </record>       <record name="Ken" />       <record name="Larry" />     </record>   </record> </record>

but don't know how to get the 'children' tag in there, and then how to convert it to json format ?

How do i generate a json file from hierarchical organization data using SQL ?


texts: How do I script SQL Server 2012 replication to turn it off during a deployment and then turn it back on after the deployment?

texts:
The scenario that I'm in is:

We have a database that is replicated but its objects (tables, etc.) are updated via Entity Framework. Whenever a schema change is made to the database by the SEs, we need to turn off replication, allow EF to make the changes to any replicated articles, and then turn replication back on. Currently this is done manually but we want to automate it for Octopus Deploy.

What is the process to script turning off replication and then turning it back on?


texts: How to write correct array string parameter for mysql query?

texts:
I'm trying to put up a correct string to pass as a parameter to a simple query.
Correct query that returns records:
SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04');

...but if I pass this (''2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04'') as a parameter it doesn't work.
I also tried to pass this ('2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04')
... but it also doesn't work.
My idea is to put this string as a parameter through a procedure.
Perhaps like this:
IN parameter TEXT

BEGIN

SET @c = CONCAT('SELECT id_s FROM group_of_id_s
WHERE date IN (', '''' , parameter, '''', ')');

PREPARE stmt from @c;
EXECUTE stmt;

END

any reference or helpful advice would be appreciated.
EDIT: Based on very helpful example from @nbk and changing date format before comparing it to an array, I managed to put up a valid query that works like a charm. You can see it below in all of its glory. ;O) Response from @nbk is therefore considered as solution. Thanky you very much @nbk.
SELECT id_s FROM group_of_id_s
WHERE find_in_set(DATE_FORMAT(`date`, '%Y-%m-%d'), parameter) > 0;


texts: join the two tables which possess one to many relationship

texts:
In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' &
                             Table2:'Ex'
description of my content_field_mem_id is

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid                | int(10) unsigned | NO   | PRI | 0       |       |
| nid                | int(10) unsigned | NO   | MUL | 0       |       |
| field_mem_id_value | int(11)          | YES  |     | NULL    |       |
| sid                | int(30)          | YES  | MUL | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

The member Id values are inside the field_mem_id_value field.
Another Table Ex description is shown below :

mysql> desc Ex;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| sid           | int(30)     | NO   | PRI | NULL    | auto_increment |
| mem_id        | varchar(30) | YES  |     | NULL    |                |
| nature_of_due | tinytext    | YES  |     | NULL    |                |
| amount        | int(30)     | YES  |     | NULL    |                |
| date          | date        | YES  |     | NULL    |                |
| paidby        | tinytext    | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

The above table Ex doesnt contain any values.i.e)the Ex table has 0 rows. After posting operation by the admin Ex table will gets affected.

My aim is to show the view to the user using the Ex table.

The content_field_mem_id table consists 1000 numbers of member id which was imported by me and inserted into that content_field_mem_id table.

The relationship between this two table is one to many relationship i.e) Each member can pay any of the fees . But member fees is must so one member can have many transactions on the second table.

How to join the both tables and how to set the foreign key constraints?. I have set the foreign constraint for the mem_id ,it throws me an error not an syntax error.

I want to join the tables and the second table has to be taken for the view purpose and second table member id can be duplicated cos of many transactions.


texts: Azure elastic pool: Can I create as many databases as I want?

texts:
I am sitting here trying to figure out what the elastic pool in Azure gives me. I have a customer that wants a guess on pricing if they move to Azure. They have 3 databases, on a server. The server has 96 GBs of RAM and a 3.4 Ghz processor. That is all I know. The three databases are sharing these specs, and each database has it own area of expertise:

one database handles all the CMS data
the second database handles all the commerce data
the third database is a database that stores user info, etc - all data that doesn't fit the two others

I think the usage pattern is pretty diverse, so sometimes the commerce DB is using a lot of RAM and CPU, other times it is the CMS DB etc.

I don't think they fit well into a single database, because they are going to need three big databases then, and almost all of them are going to be under-utilized all the time. So I thought about elastic pools.

The description of elastic pools fits the above very well: all databases share eDTUs. Perfect! But one thing the docs don't describe is: do I pay for the elastic pool AND per database? Or do I just create databases on the fly, and all of them share, that is: the databases are free, but you pay for the eDTU?

I have read this https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool - but it doesn't describe my situation.

Hope someone can give me some more info.

EDIT
They are going to update the docs: https://github.com/MicrosoftDocs/azure-docs/issues/49201#issuecomment-593579476. I created an issue for this asking for clarification.


texts: Is it necessary to drop foreign keys before adding a null column to a table and then re-add them?

texts: I have generated some SQL Server(2008) upgrade scripts using SqlCompare.  The primary objective is to add a null column.  However, the generated script wants to drop foreign keys in a couple of other tables and the primary key of the target table before the add column.  It then re-adds them.  Is this necessary?  What is the reasoning behind doing it?.


texts: Is there a way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases?

texts:
I have a master table, and several remote read-only materialized views on pre-built tables that are being fast refreshed to keep in sync with the master table, using a dblink.

The table definitions (master and remotes) are currently identical.  I want to add a column to the master table, and eventually have any changes propagate to the remote tables...without breaking anything :)

I don't have much experience with this, so I'm not 100% sure how to proceed.

My current plan is:

Add column to master table
Add column to remote tables
Drop the materialized views on the remote tables
Recreate the materialized views on the remote tables, but with the added column.

What I want advice on is whether this should work, or do I need to do something with the materialized view log on the master as well?


texts: Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)

texts:
I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I needed to utilize input parameters. I recently received a request to add filtering so that specific products and locations could be searched as well, but I found myself executing this function N number of times, which caused a significant performance bottleneck. I figured having these conditions in one query would alleviate these performance issues.
I made a bit of progress after following the (very well-written) answers to some questions here, but I am still stuck wrapping my head around how to generate  WHERE clauses for each input array element.
Essentially my desired "output" SQL would look something like this:
select sum(total) as total_activity,
  count(1) as sales_event_count,
  created_at::date as sales_event_date,
  date_part('year',created_at at time zone 'hst') as year,
  date_part('quarter',created_at at time zone 'hst') as quarter,
  date_part('month',created_at at time zone 'hst') as month,
  date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = '12345' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or
      (p.sku = '09876' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or
      (p.sku = '10293' and p.manufacturer = 'CompanyB' and location_id = 'LocationA')
group by se.created_at
order by se.created_at

Here are some example pages that I have explored to help tackle this problem:

Pass multiple sets or arrays of values to a function
How to select multiple values into an array and loop through? (postgres 9.3)
Selecting from multidimensional array parameter in Postgres

After picking and choosing from each of these, I have come up with the following:
create type product_type as(sku character varying(100), manufacturer character varying(200))

create or replace function find_sales_location_activity(
  _products_arr product_type[],
  _location_id bigint
)
returns table (total_activity bigint, sales_event_count bigint, sales_event_date date, "year" double precision, quarter double precision, "month" double precision, "day" double precision) as
$func$
select sum(total) as total_activity,
  count(1) as sales_event_count,
  created_at::date as sales_event_date,
  date_part('year',created_at at time zone 'hst') as year,
  date_part('quarter',created_at at time zone 'hst') as quarter,
  date_part('month',created_at at time zone 'hst') as month,
  date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = $1[1][1] and p.manufacturer = $1[1][2] and location_id = $2) or
      (p.sku = $1[2][1] and p.manufacturer = $1[2][2] and location_id = $2) or
      (p.sku = $1[3][1] and p.manufacturer = $1[3][2] and location_id = $2)
group by se.created_at
order by se.created_at
$func$
language sql;

...but obviously this isn't looping over anything. I have experimented with replacing the FROM locations loc clause with FROM generate_subscripts($1, 1) and attempting to loop through that way, but replacing the table name causes my left outer join's to fail.
Clearly I'm a bit out of my depths here, but I'd really, really appreciate it if anyone could lead me in the right direction. Thanks in advance!


texts: Can't get databases or its objects with powershell

texts:
I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My current problem is that I can't list databases as I do in my dev machine like this:

PS SQLSERVER:\> ls SQLSERVER:\\SQL\server\instance\Databases\

And I get the following:

Get-ChildItem : No se encuentra la ruta de acceso 'SQLSERVER:\SQL\serverr2\serverr2\Databases' porque no existe.
En línea: 1 Carácter: 4
+ dir <<<<  SQLSERVER:\SQL\server\instance\Databases
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\server\instance\Databases:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Obviously I have no way to get Child-Items like the Stored Procedures, Views, etc.

However, I'm able to list the installed instances, and also workaround this issue by listing databases with this:

DIR SQLSERVER:\\SQL\serverr2 | SELECT Databases -ExpandProperty Databases | SELECT Name,Owner,Urn

But still need to get another objects like stored procedures, views, functions,etc.

This is the info provided by the Get-Host cmdlet

Name             : ConsoleHost
Version          : 2.0
InstanceId       : c1976472-19c0-439e-a4f6-debe59a18616
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : es-MX
CurrentUICulture : es-ES
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

and the @@version of SQL Server is Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Edit: According to $PSVersionTable.PSVersion in my server I have installed PowerShell 2.0. Also I found that I'm unable to Import-Module sqlps but for some reason I'm capable to dir as I already described (with the current problems obviously).


texts: How does mysql deal with queries that touches myisam and innodb tables?

texts:
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS

If a query involves both myisam and innodb tables, will the query use table lock or row lock on innodb tables?
Is this a good practice?


texts: Maintaining permissions on a replicated view after reinitialization

texts:
When we were on SQL Server 2005, there was some way that I was able to keep custom permissions on a view at the subscriber.

Now that I'm on SQL Server 2012, there doesn't seem to be a way to do that.

In the article properties of a table, there is the "Action if name is in use" property "Truncate all data in the existing object" which will keep any permissions on the table.

However, views do not have this option. It is either "Keep existing object unchanged" or "Drop existing object and create a new one." If I update my view, I want the changes to be reflected in the replicated view, but I don't want my custom permissions to disappear.

Am I looking at starting a post-reinitialization script for this purpose or is there something I'm missing?


texts: Log shipping and log file: Can the log on the master or slave every be truncated/shrunk?

texts:
SQL Server 2008, standard log shipping setup (well, using redgate, but it is a pretty normal flow).

On the replica, the log file is pretty big.

Wondering if on the backup/replica servers it is OK to shrink the log in some way?

Thanks!


texts: SQL Server 2016 - unexpected privileged time bursts

texts:
MS SQL 2016 SP2 CU8 + Always On.

1 CLR module (occasional usage, no correlation with the issue)

I'm looking for methods to find the reason for such behavior. DBCC FREEPROCCACHE solves. But after approx. 10 such "solving" attempts I have to initiate failover. If not I may get an outage with stopping of query processing at all.

I tried to remove from the cache not all plans but some. Still have no stable progress here.

So, does anybody know a way to analyze privileged time consumption? And - yes - privileged time is consumed by the sqlserver service, confirmed.

The current workaround is running FREEPROCCACHE in case privileged time is greater when 7% at least for the 10 seconds and weekly manual failover. But this approach is not good enough for me.

In response to some requests for additional information:

The privileged time is not correlated with IO.  Actually, I tried to find any correlation including perf counters, waits and spinlocks but found nothing.
That server has columnstore only in the "tech" DB with occasional usage patterns. I.e. we can count "no columnstores in the active usage".
The server is a physical machine, not a VM. >
"What are you actually trying to solve here?" When the problem occurs, I may get an outage with stopping of query processing at all.

For "end users" this looks like server stopped to process their queries.

Disk drivers are up to date
Disk and Network IO are OK and I see no correlation with the issue

Here are assorted perfmon counters at the beginning of a typical reproduction of this problem:

And the same counters at the end of the repro:


texts: Create triggers on slave only in mysql replication with a slave with different schema

texts:
I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the slave which I use for sync with a third system. I need the following behavior:

By default, the synchronized column is set to 0
I have an external python script that copies each row with synchronized=0 to another system (with another schema) and then sets the synchronized field to 1
If the row is updated again by the master through replication, I want the synchronized field to be set to 2 (probably through a trigger on update) so that my python script can detect updated rows and update them in the third system.

To simplify, let's say we have a master table like this :

People
id (int)
name (varchar)

and the corresponding table on slave

People
id (int)
name (varchar)
synchronized (tinyint)

If I insert
I already tried to set up a trigger on update on the slave to change the synchronized field to 2. It works when I update a row manually but not when it is updated through replication.

I understood that in row-based replication triggers are not replicated from the master but I am not trying to replicate a trigger from the master but rather create a trigger on slave whenever a row is updated through replication.

Right now my replication is row-based but i could change it to statement based or mixed if needed.
Hope it makes sense and that everything is clear, any suggestions are welcome,

Thanks


texts:
I use below query to search for a substring in whole xml(including node name and node value)

SELECT *
FROM tablename
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

I want an alternative query which has good performance than this. So please suggest some.
Below are details:
Table:

CREATE TABLE [dbo].[tablename](
    [Sl_no] [int] NOT NULL,
    [Date] [date] NULL,
    [Operation] [nvarchar](max) NULL,
    [Allot] [nvarchar](50) NULL,
    **[xmlcolumn]** [xml] NULL,
    [By] [nvarchar](255) NULL,
    [Dept] [nvarchar](255) NULL,
    [Db] [varchar](255) NULL,
    [tabl] [varchar](255) NULL,
    [Remark] [varchar](5000) NULL,
    [Work] [int] NULL,
    [F2] [nvarchar](max) NULL,
    [F6] [nvarchar](max) NULL,
    [F5] [nvarchar](max) NULL,
    [F8] [nvarchar](max) NULL,
    [ListC] [nvarchar](255) NULL,
    [pro] [nvarchar](max) NULL,
    [Completed] [varchar](50) NULL,
    [WorkTime] [xml] NULL,
    [RelatedData] [varchar](255) NULL,
    [User] [xml] NULL,
    [TeBy] [xml] NULL,
    [Date1] [nvarchar](50) NULL,
    [Num1] [nvarchar](50) NULL,
 CONSTRAINT [PK_DBChanges] PRIMARY KEY CLUSTERED
(
    [Sl_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Number of rows: 3000
Sample xml in a column:(number of nodes may change little bit.

<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>

Type of search expected:To search for a substring within xml column 'xmlcolumn' and then return the full row containing it.

So the query i used is

SELECT *
FROM tablename
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

The IO for the query by using set statistics time on

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 93 ms.

I tried with another query(But it will search only in the node value)

select *
from tablename
where xmlcolumn.exist('//*/text()[contains(., "abc")]') = 1

and statistics output was

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 109 ms.


texts:
I want to understand a database layout.

It has 180 tables.

I only know the high level use case: Owncloud (File Hosting Server. Can be compared to DropBox).

How to understand the database layout?

How to identify the most important tables?

Is there a tool which helps me to understand the layout?

At the moment am interested in two facts:

ForeignKey Relations: It would be nice to have graph
Number of rows in the table.

I use PostgreSQL 9.5.5


texts:
Can you run Distinct() on XML?  I ask because if you look at this garbage data, it produces this error

  output
  SQLState = 37000, NativeError = 402
  Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types char and xml are incompatible in the add operator.
  SQLState = 37000, NativeError = 8180
  Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
  NULL

How can I succesfully run this bcp statement with Distinct and Cast to XML?

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(DISTINCT(pinkpurpleblue) As XML) FROM @TestData''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql

Even with this syntax I get the same error, and (I think) I am only attempting to cast a nvarchar column to xml in this example

Declare @TestData Table (pinkpurpleblue varchar(max))
Insert Into @TestData (pinkpurpleblue) Values
('xx11'), ('rr22'), ('xx11'), ('rr22')

Create Table #Test123
(
  pinkpb varchar(max)
)

Insert Into #Test123 (pinkpb)
Select DISTINCT(pinkpurpleblue) FROM @TestData

Declare @file nvarchar(200), @sql nvarchar(max)
Set @file = 'C:\Prod\Results\testing.csv'
Set @sql = 'bcp "Select ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpurpleblue     As XML) FROM #Test123''" queryout "'
Set @sql = @Sql + @file + '" -c -t, -T -S '+@@SERVERNAME
exec master..xp_cmdshell @sql


texts: How to increase the value of a column in a table by a constant by consulting another table?

texts:
I have prepared the database of the college given as

create table depts(
deptcode char(3) primary key,
deptname char(70) not null);

create table students(
rollno number(2) primary key,
name char(50),
bdate date check(bdate < TO_DATE('2004-01-01','YYYY-MM-DD')),
deptcode char(3) references depts(deptcode)
on delete cascade,
hostel number check(hostel<20),
parent_inc number(8,1));

create table faculty(
fac_code char(2) primary key,
fac_name char(50) not null,
fac_dept char(3) references depts(deptcode)
on delete cascade);

create table crs_offrd(
crs_code char(5) primary key,
crs_name char(35) not null,
crs_credits number(2,1),
crs_fac_cd char(2) references faculty(fac_code)
on delete cascade);

create table crs_regd(
crs_rollno number(2) references students(rollno) on delete cascade,
crs_cd char(5) references crs_offrd(crs_code)
on delete cascade,
marks number(5,2),
primary key(crs_rollno,crs_cd));

Now I want to give Grace Marks 5 in subject ‘DBMS’to the students who have scored less than 50 in that subject.

Accordingly I wrote the query as:

update r
set r.marks=r.marks+5
from crs_regd r
inner join crs_offrd o
on r.crs_cd=o.crs_code
where o.crs_name='DBMS' and r.marks<50;

but it shows error as SQL command not properly ended in line 3. I am using ORACLE SQL. What is the problem in this query?


texts: inserting the table colomn data in multiple tables colomn.(SQL SERVER)

texts:
I want to insert one table colomn data into multiple tables colomns using triggers.
For Example: I have one table name 'T1' having multiple colomns like 'C1','C2','C3' and now I want to insert these colomns data in table 'T2','T3','T4' having same colomn names 'C1','C2','C3' by using trigger.

Can anybody please help me out?


texts: Database Administrator vs Database Architect?

texts: In terms of job responsibilities and activities, what is the difference between a Database Administrator (DBA) and a Database Architect?


texts: What is a suitable,stable, high performing database for Large Financial Time Series Data?

texts:
I realise this question may have be answered in some form but I would like to gather the thoughts and opinions of actual users with experience in this field of Database design and development. I have a 'large' financial time series database, close to 150GB and growing at a rate of about 4GB per day. Currently I am using MySQL as my database engine, however I am concerned about scalability and performance in future. I have read conflicting reports on alternative solutions (MySQL, Postgres, Cassandra, Datomic, KDB+ etc etc), and with the 100s of Database technologies available one is left somewhat unsure. That's why personal experience of users I think would be invaluable.

Before anyone mentions the obvious such as KDB+, the issue I have with that is that it is predominantly an 'in-memory' database - not ideal for me. I understand it would be great for high frequency traders but that is not the nature of my research.I would prefer a scalable, on disk, SQL solution? Happy to hear thoughts and discuss.


texts: Install Access Database Engine on Linux Centos

texts:
I want to read mdb file from linux centos.  To read that file, we must already install access data engine. Is there any access data engine for linux?

Now I'm using mdbtools to configure ODBC connect to mdb file inside Centos
I'm edit odbc.ini and odbcinst.ini inside etc directory.

odbc.ini:

[mydb]
Description = Microsoft Access Try DB
Driver = MDB
Database = /tmp/mydb.mdb
Servername = localhost
Username =
Password =
port = 5432

odbcinst.ini:

[MDB]
Description = MDB Tools ODBC
Driver64        = /usr/lib64/libmdbodbc.so.0
Setup64         = /usr/lib64/libmdbodbc.so.0
FileUsage =
CPTimeout =
CPReuse =

Then, in terminal, I type:

isql -v

result:

[IM010][unixODBC][Driver Manager]Data source name too long
[ISQL]ERROR: Could not SQLConnect

My question, how to fix this? I'm sorry for my bad English.


texts: Privileges required to install PostgreSQL

texts:
I am new to PostgreSQL Database. I need to install it in my machine.

I am not administrator in my system.

Can some one suggest, if  Non-Administrator can install it.

If there is any other way of installing apart from .exe, please let me know.

Thanks In Advance.


texts: SQL Server: how to troubleshoot login timeout expired error

texts:
I have a production SQL Server 2012 (SP1) running on Windows Server 2008 R2 Enterprise edition.

A lot of time while connecting to this server from a remote host I to face connection timed out error. Developers also reported same error from their scripts as well.

Below is the error which I used to get from Management studio:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

However this is not so frequent and in next retry I use to connect to database.

From Python Scripts below is the error:

  11/05/2015 10:21:01 AM [ERROR] ('HYT00', '[HYT00] [Microsoft][ODBC SQL
  Server Driver]Login timeout expired (0) (SQLDriverConnect)')
  11/05/2015 10:21:11 AM [ERROR] global name 'traceback' is not defined

Above error is reported a lot of time by the scripts. In the SQL Server error log I didn't find any issue. Is there some issue with the SQL Server driver or with SQL Server?


texts: sqlite: attached database doesn't work properly

texts:
1) I can open database from the command line:

bash-3.2 # sqlite3 RecordingDb.sqlite
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.

sqlite> .databases
main: /db/RecordingDb.sqlite

sqlite> select * from recording limit 1;
|110|4||1423222200|1423049398|1423051200|0|0||0|100|||0|0||||1013760|1|11|4|19300||4|4|1801|||4||0|0||||20486|||

2) I can attach it from the sqlite prompt, but it doesn't work properly:

bash-3.2 # sqlite3
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> attach database 'RecordingDB.sqlite' as RecordingDb;

sqlite> .databases
main:
RecordingDb: /db/RecordingDB.sqlite

sqlite> select * from RecordingDb.recording limit 1;
Error: no such table: RecordingDb.recording

Why?

Similar question:

Working with multiple databases?


texts: On checking the validity of a large set of identifiers

texts:
In the research group where I work, we must solve following problem hundreds, maybe thousands of times every day: given a set of putative gene names (typically a few hundred of them), flag those that are not in our (MySQL) database.  This problem is solved in a number of ways by our various applications and scripts.  I would like to optimize the process.

The simplest approach, of course, is to iterate over the list of gene names (after removing any duplicates, of course), and for each gene name perform something like

SELECT count(*) FROM gene WHERE genename = 'GENENAME-XYZ';

or

SELECT * FROM gene WHERE genename = 'GENENAME-XYZ';

and detect the condition where the search fails.

This means performing one such query for usually hundreds, often thousands, of genes.

(BTW, most of these queries are usually performed by scripts written in Python, R, Perl, MATLAB, etc. using those language's DBI facilities.)

Alternatively, one could concoct a (typically huge) SQL statement of the form

SELECT DISTINCT genename FROM gene \
    WHERE genename IN ('GENENAME-001', 'GENENAME-002', ..., 'GENENAME-789');

...and from the result deduce the subset of invalid gene names.

Maybe it's just me, but I pick up some potent code smell from such huge SQL statements.

Bottom line, I don't have an entirely satisfactory solution, but since the problem must be very common, I figure that there must be standard solutions for it.  If this is correct, please let me know what they are.

BTW, assume that access to the DB is read-only.  In particular, please rule out any solution that involves creating temporary tables.

Also, in case it matters, ours is a MySQL database.


texts: Joining Tables in the FROM vs. the WHERE clause

texts:
First time caller here.

I have a question regarding best practices when joining tables. For example, both of the following queries return the same results:

SELECT i.id, p.first_name, p.last_name
FROM individuals i, profiles p
WHERE i.id = p.individual_id;

SELECT i.id, p.first_name, p.last_name
FROM individuals i INNER JOIN profiles p ON i.id = p.individual_id;

What are the pros / cons of these two approaches? Please let me know what you think. I'm interested in performance differences, but also in readability of the query, portability from one RDMS to another, etc.

Thank you!


texts: Extract latest entry for each currency type from table


texts: Is a "Ranking" query's ORDER BY in FROM subquery is preserved?

texts:
I've read in the following links that the ORDER BY in FROM subquery can be ignored.

Quoted from MariaDB knowledge base

A query such as

SELECT field1, field2 FROM
(SELECT field1, field2 FROM table1 ORDER BY field2 ) alias

returns a result set that is not necessarily ordered by field2.

So, given the following query:

SELECT CASE
         WHEN   @prev = x.city_id
            THEN @a
         WHEN @prev := x.city_id
            THEN @a := @a + 1
         END
         `rank`
FROM
(SELECT @prev := NULL, @a := 0) y,
(
    SELECT *
    FROM sakila.address a
    ORDER BY a.city_id
) x

Will the rows returned from the FROM subquery be in the order specified in the ORDER BY clause?

Edit:

How about the query below, will the x.city_id be ignored or not?

SELECT ....
FROM (
    SELECT CASE
           WHEN   @prev = x.city_id
              THEN @a
           WHEN @prev := x.city_id
              THEN @a := @a + 1
           END
             `rank`
    FROM
        (SELECT @prev := NULL, @a := 0) y,
        sakila.address x

    ORDER BY x.city_id
)
ORDER BY another_order_by_clause

How I solved it.

After looking through the answer and feedbacks from @ypercube and @jkavalik, I decided to explicitly create a temporary table for each inner query that I had.

Reason being as pointed by them, adding LIMIT by a huge number or other hacks might not work in every version of MariaDb or MySQL.

Performance was still good for the query that I did.

e.g:

The second query above is changed to

   CREATE TEMPORARY TABLE temp1 SELECT CASE
                   WHEN   @prev = x.city_id
                      THEN @a
                   WHEN @prev := x.city_id
                      THEN @a := @a + 1
                   END
                     `rank`
            FROM
                (SELECT @prev := NULL, @a := 0) y,
                sakila.address x
            ORDER BY x.city_id;

    SELECT ....
    FROM temp1
    ORDER BY another_order_by_clause;