texts: Why is the next scheduled run date for my SQL Server job NULL?

texts:
I've been dealing with the Microsoft SQL Server Agent with regards to scheduling jobs.

I'm trying to find out what the next scheduled run date for a bunch of jobs that were created where the job ids from the msdb sysjobs table(s) joins back to my specific system tables.

We store the job ids that get created in sql server agent in our own system table in order to join back.

I've now discovered the sysjobsactivity table. It depends on a session. I may have a job that had a next scheduled run date in an earlier session, but a NULL value for that field in the latest session.

Restarting sql agent is at least one way to create a new session id. It looks like all existing jobs get an entry associated to the new session but the next scheduled run date doesn't necessarily move forward.

I'm pretty sure if I update a job with criteria that places all occurrences in the past, the next run date will be NULL, which makes sense.

But I do see cases where I have a recurring schedule with no end date, and the next scheduled run is NULL which seems wrong.

It's also strange that some of the values in msdb.dbo.sysjobschedules.next_run_date and  msdb.dbo.sysjobschedules.next_run_time differ from the value stored in msdb.dbo.sysjobactivity.next_scheduled_run_date.

The sysjobschedules tables is saying the next run date is the occurrence after the value claimed as the next run date in the sysjobactivity table.

You'd think those would get calculated the same way and would be equal.

How do I correct this?


texts: SQL Server — optimization techniques for rebuilding very large table

texts:
We generally use SQL Compare to generate upgrade/change scripts, and we also use the "Force column order" option by default. This basically means that the column order will be preserved going from database version A to B, so in situations where this is relevant a table rebuild becomes necessary.

Well, somebody decided it would be a good idea to sandwich a new column in-between existing columns when making a schema change to a table containing a very large amount of data (obviously ill-advised). Here are some more details of what I am faced with:

The table has about 1.3 billion rows
A clustered index exists on the table
A few non-clustered indexes exist on the table
The server the database instance is running on is very limited from a resource perspective

The script generated by SQL Compare handles this by dropping all indexes on the table, throwing the data into a temp table, dropping the table, re-creating the new table, and inserting the data from the temp table into the new table schema. I feel like this can be optimized. Here is what I came up with to do so:

bcp data out to file
disable indexes
truncate table
rebuild table based on new schema
bcp data into new table schema
enable indexes

One obvious problem is at some point the table has to be dropped and recreated if I am not mistaken since the column order is changing. Dropping the table will also drop the indexes that I would like to keep in place, wouldn't it? Has anybody been faced with a similar situation? Any advice as to whether or not my plan looks like a better one to reduce potential down time? Any ideas or advice will be greatly appreciated.


texts: Any advantage of creating table in temporary tablespace - Oracle

texts: My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?


texts: Multi-source replication from older masters

texts:
I have several different production databases running MariaBD 5.5. I'd like to setup a new machine, with MariaDB 10.0 in order replicate all of those databases to a single MariaDB instance using multi-source replication.

I know that multi-source was only introduced in MariaDB 10.0, but does it require that both master and slave are running the same version, or will it be sufficient for only the slave to be on MariaDB 10?

Updating all of the production databases to v10.0 isn't really an option at this stage unfortunately.


texts: Why am I getting an error if I am trying to modify data type?

texts:
I am facing an error when I passed the following syntax in MySQL:
ALTER TABLE store MODIFY (DOB char(40)) ;

I am attaching a screenshot too because then you can see that the column DOB is having all rows NULL in it. Even after that Data type isn't changing.

See in yellow is my syntax. Earlier, DOB was an integer. See all the rows are NULL. Then why the error!
Please, help even if you feel it is a silly question. I am just a school going kid who has this MySQL as my subject and I don't have any tutor too.


texts: Postgresql database backup (and restore) from two servers to third storage place

texts:
I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2.
I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a 'storage' machine (C) where I have postgresql-commons clients installed (9.3).

Which is the cleanest/better way to obtain my goal?

I've made scripts on (C) to use pg_dump to make local backups from (A) and (B), but being a newer version I've problems with restore, also when using Plain sql format.

Do I need to install clients pg_dump/restore for 9.0 and 9.2 version on the client machine (C) and if so, how? thanks.


texts: What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?

texts:
I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.

However, I found out that POINT(X,Y) also works. I didn't find any description why GeomFromText should be used instead of POINT.

For example I have the following simple relation:

CREATE TABLE Site (
    SiteID      BIGINT UNSIGNED,
    Position    POINT
);

And I can insert values using the following two variants:

INSERT INTO Site (
    1,
    GeomFromText( 'POINT(48.19976 16.45572)' )
);

INSERT INTO Site (
    2,
    POINT(48.19976, 16.45572)
);

When I view the table (SELECT * FROM Site) I see the same binary blob for the location, and when I view the coordinates (SELECT *, AsText(Position) FROM Site) I also see the same values.

So why should GeomFromText be used? Are there any (known) performance differences between these two variants? How is this solved in other database systems than MySQL?


texts: Mysql table doesn't exist error, references wrong table name

texts:
I have a table that I can query fine...

SELECT * FROM search.books LIMIT 1;

Returns a value correct.

However, when I try and update the table...

UPDATE search.books SET title='sometitle' WHERE id='12345'

It returns

ERROR 1146 (42S02): Table 'search._books_new' doesn't exist

I'm not sure why it's referencing '_books_new' and not the one specified ? It's possible the table was copied previously as it's a dev server.

Engine is MyISAM

I have tried a

check table search.books

Also have restarted mysqld, but no difference.


texts: Can anyone explain to me the concept "incarnation" in Oracle database in an easy-to-understand way?

texts:
The Oracle documentation describe incarnation as:

  incarnation

  A separate version of a database. The incarnation of the database
  changes when you open it with the RESETLOGS option, but you can
  recover backups from a prior incarnation so long as the necessary redo
  is available.

But I still can't fully understand it. Can anyone explain it in a easy-to-understand way to me please?

Thanks,


texts: MySQL/Percona Audit Log Plugin Not Working

texts:
I've been looking to implemement the Percona Audit Log plugin. So far I have installed it and configured it, and it seems to be working ok.

Except, I have installed it on a slave Database as I don't want to run it on my master database (because of the additional overhead, and the fact it requires a DB restart if I want to configure it), and it doesn't appear to be logging any queries.

I have set up a script to import the logfile back into a standalone MySQL instance so I can query it, and from what I can tell it only records events that happen locally on the slave, but ignores all queries that are replicated from the master.

I implemented it because I need a way to audit the types of statements being run on our databases (ALTER, UPDATE etc.) but if they're not going to be recorded it's not much use.

I can't see anything in the documentation for this (either Percona or MySQL). Does anyone else use this, and is it just me that has missed something or am I going to have to go back to the drawing board.

I'm running MySQL 5.5 with Statement Based Replication.


texts: For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through the UI?


texts: Query to get Object_ID and Dimension_Name from DMV

texts:
I am looking for a DMV query which contain Dimension_Name and Object_ID together.

I have System.mdschema_dimensions to get the Dimension Unique name and System.discover_object_activity to get the Object_ID.

They showed together in the dimension properties and I'm interested if there's any DMV which shows them together.


texts: Impact of changing char(10) to varchar(12) on massive table

texts:
I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows.

It has a column called 'balance_forward' that is char(10)   (Unusual choice for a numerical column but I didn't design the table)  I need to increase its size to accommodate balances that are more than ten characters in size (xxxxxxxxx.xx)

I tried changing it to char(12) (risky,  I make no excuses) but I think* this caused the database's log file to grow many gigabytes larger (and fill up the log drive) and the operation failed anyway (data type is still char(10))

I later realized that it would make more sense to change to at least varchar(12),  that way the column won't forcibly take up more space, but it will have more room to fit larger data.

My question is - will this also cause the log file to grow again (I managed to free up some space by moving other files off the log drive)

And am I correct to assume that using varchar instead of char will prevent the existing data from taking up more space?

(Ideally the data type should be changed to the most appropriate type for financial balances,  but I figure that will be a more drastic change on 58 million rows)

*I can't be sure.  I thought I saw plenty of space on the log drive before the operation, but I might have seen 'mb' and mistook it for 'gb'. So it may be that the log had already filled the drive. Apparently logs can grow until they've filled their allocated drive/space


texts: How to access a shared folder from Windows Cluster IP

texts:
This is a very strange question, but I am trying to access a Windows file share from a SQL server cluster IP, that resides on that server. In the past and on other servers I have, I get connected to the share on the primary node. I just stood up a 2012R2 cluster and when I try to access the cluster IP I cannot get to any shares. It takes me to an empty share and does not let me navigate out of the share. Has anyone experienced this? Is there any way to resolve this? Any tips are appreciated.

Thanks!


texts: Is there a negative effect if I place an index on every column in a wide table?

texts:
We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can select any number of columns, rows; they can filter by, group by, sort by and aggregate by any columns they want.

This table is actually 'precomputed' from a set of relational tables; this precomputed tables is 90 columns wide and will be used solely for the users' queries. This big flat table will be produced just once a day.

Because we have no idea in advance what they will actually be doing these operations against, we were suggested to place an index on every column of the table.

My question is, would this have any adverse effects?

We would also want this 'methodology' to extend to future datasets that we provide similar functionality on; 1.7 million rows with 120 columns

For reference, we are using SQL Server 2008 R2.


texts: Gathering information to tune oracle row prefetching

texts:
I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips without excessively increasing memory usage.

To do this, I need to gather information on queries either from the application or from the database. Can oracle provide disaggregated historical information about

how many rows were in the results of queries
how much data was returned for queries

Here is an example of the type of report I'm interested in generating:

  On the instance named foo, 20,000 queries were executed on Monday.

  60% of the queries returned fewer than 100 rows, 20% returned
  between 100 and 1000 rows, and 20% returned between 1000 and 10,000
  rows.
  50% of the queries returned less than 1KB of data, 30% returned
  between 1KB and 10KB, and 20% returned between 100KB and 1,000KB

If the database server cannot provide this information, but you have ideas for other ways I could gather it, feel free to answer with those.


texts: Can I disable execution plan caching for debugging purposes?

texts:
I am trying to isolate poor performing queries in our application.

While query execution plan caching is great in production it is interfering with our debugging efforts.

Is there are way to temporary disable execution plan caching in SQL Server (any further) so I can get a reliable & true indicator of the parse & compile time on SQL each time I run the query batch?


texts: Excel Values Uploading as NULL to database

texts:
I have an Excel file in which I have many records. When I upload this file to a table in a database (SQL Server) some values save as NULL even though the Excel file does not have NULL or blank fields.

I double checked all data types of the columns to make sure they are of the correct data type but no luck.

However, I tried pasting the values in question into a Notepad file, and pasted  them back into the Excel from Notepad and this worked. The values no longer save as NULL if I do this.

What could be the reason this occurs? Why does the database not "see" these values in Excel?
Is there a quicker way I can check the data before uploading?


texts:
I know in Oracle

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(in_COMP);

Now I writing procedure in Teradata

create procedure SANDBOX.CreateNewUser ()
begin
    DECLARE errstr VARCHAR(1000);
    begin
        --begin
--  SET return_code='Success';
--end;
--begin
    /* here have to your code*/
--end;
    end;
end ;

How make same in Teradata?


texts: How to search one bible verse from a source that may contain ranges?

texts:
My WordPress system contains posts with a field containing Bible references and ranges, eg.

John 5,1-10; Matthew 3,2; Mark 1,1-5

Users searching for a Bible references e.g. John 5,3 or Matthew 3,2 or Mark 1,2 would all need to find the above post.

From an SQL point of view is this even possible? If not what would I have to change in this approach?


texts: Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001

texts:
We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update.

Rebooting the server fixes the problem.

I changed the maximum server memory (in MB) to 2000 because it's a 4GB VPS.

But I just don't know what's causing the error.


texts:
I have a web site which started to have performance issues because of a mysql query which is executed for each request.

I have the following table structure.

Post table (125000+ records)
id: primary key,
index on thread_id and type columns

Thread table (48000+ records)
id : primary key

I'm running the following query

select t.id, t.subject, count(distinct p.id) as postCount
                from thread t
                inner join post p on p.thread_id = t.id
                where p.posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                and p.type = 0
                group by p.thread_id
                order by max(p.posted_on) desc
                limit 0, 25

mysql explain result :

Mysql profile :

As you can see from profiling response main problem is copying the tmp table part. I tried increasing the max_heap_table_size and tmp_table_size parameters to 256MB.


texts: ALTER TABLE won't accept a series of operations

texts:
According to the example from the PostgreSQL 12 docs it should be possible to make a series of operation within one ALTER TABLE command. But when I do the following it gives me a syntax error at the possition of the comma:

CREATE TABLE a (id INT, name TEXT);
ALTER TABLE a
    RENAME COLUMN id TO di,
    RENAME COLUMN name TO eman;

What is wrong here?


texts:
On a high load production server I have many SQL selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but these queries are still cached with OS cache. The server has 64G ram and used ram is still 100%, about 47G is OS cache. If I manually drop OS cache with sync; echo 3 > /proc/sys/vm/drop_caches, then everything run more smoothly. But in few seconds the OS cache grows again to about 47G and uses all free ram. And I know, most of this data will be read only once, so there is no point for caching.

Is there any way to say to OS - "I know, I will not read this data again, don't cache them." ? Would be great, if this can be set for single mysql query or process.

And one more thing, I know about innodb_flush_method=O_DIRECT, but these tables are Aria engine on MariaDB


texts: File share witness vs 3rd Node for WSFC + AG with only 2 nodes?

texts:
I am migrating from Mirroring to AGs on SQL Server 2014 / Windows 2012 R2. I will have a 2 node cluster. I need this cluster to be online at all times for a production environment.

I am reading Microsoft documentation for Availability Groups but I cannot find anything along lines of "Best Practices" for having a 2 node AG/WSFC, except that not having a witness is NOT "Best Practice". I am simply exploding with questions.

If I implement a File Share Witness

What is the best place to put my (file share) witness? Is putting it on a DC a good idea or a security risk?

Why does it have to be Windows Server as opposed to (say) a linux/NAS?

How do I prevent someone from simply deleting the file share because they don't know what it is or what its for, while not locking myself/all sysadmin out of it?

If I don't Implement a File Share Witness

Could I just remove dynamic quorum and exclude witness altogether? Is that even possible?

Is it better to just add another SQL node to my cluster, if I have one, to act as a 3rd voting party but not include any of its databases? Is that perhaps more durable?


texts: Can SQL Server 2008 R2 extended events execute stored procedure or query?

texts:
I'm trying to use extended events to track queries that never complete using Event Pairing as a target.  (see this question I asked a few days ago).  The event pairing target should let me find queries that started but never completed.

My new problem is that I need to know what else was happening on the server when this event happens.  I'd like to execute a stored procedure like sp_who, sp_who2, sp_WhoIsActive, etc. whenever I find a query that has not completed in 20+ seconds, for example.  Is this possible?


texts:
I am trying to find out the database name of this file.

I opened it via hex editor:

http://gyazo.com/5b579bd2ef9620aa833bf0743a480230

Any help is appreciated.

Client sent me this file. It is very old of his DOS program but not sure which database it is.

Any help is appreciated.

When I open it using Notepad I see this:

 €D                                                                      D                         147306154      340000          ‡      P„                                    Ã                     124126290      340001        O‰      †                                    Ä                     124126323      340002       ÀDŠ      ‡                                    Å                     124126289      340003        BŠ      ‡                                    Æ                     124126202      340004
     @Š      ‡                                    Ç                     124126253      340005
     €{Š      `‡                                    È                     124126244      340006       $‹      h‡                                    É                     124126245      340007


texts: Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement?

texts:
(I think the answer is probably "no", but I haven't managed to find anything where anyone tried to do this. Which maybe is a sign in itself).

So, I have a parent and child table than I'm generating data for in a script. The PK on parent table is an autosequence, the FK on child table into the parent table is the autosequence PK which isn't populated until the parent table's data is loaded.

When I load (much smaller datasets) as SQL in PGAdmin, I can match the records together by using another column as a lookup (eg, I have a column Reference on parent table which I fill with a unique pre-defined value for each record, effectively as a surr)

I load the parent table through COPY, and that works fine. However, in the child table, what I want to be able to do is inculde (select parent_id from parent_table where reference = predefined value) as the value for the Parent ID field.

The problem I'm trying to address is that, at the point the child table's values are created, I don't know what the FK values need to be, because the parent table's value haven't been loaded yet.

To be honest, this probably isn't even a good way to go about this - I have 2 million records in the parent table and 3-4 million in the child, so I don't really want to be making 4 million time 1 record calls into another million record table, but I can't see any obvious way around this without splitting the data generation so that the child table data is generated after the parent table has been loaded.

Better ideas gratefully received!


texts: Inserting all csv files into a table in MySQL

texts:
I am looking for a way to insert all my csv files into MySQL without having to write several LOAD DATA INFILE statements.

I have many csv files I need to insert and the files themselves are very large.

I have tried *.csv, but this does not work.


texts:
We just discovered another server with a database on it. The database is owned by a SQL account SA, with the public and sysadmin roles. The other login is BUILTIN\Users, with only public. I can only connect through this one. SA is disabled, and even if it wasn't, we might not know the password.

Question: Is there any way to enable SA, or create another sql login with sysadmin?


texts:
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in sorting it.
Environment

SQL Server 2019
StackOverflow2013 database (50GB version), Compat Mode 150 (problem is not specific to this version)

The setup:
Firstly, I've created a view to return everyone with a high reputation:
CREATE VIEW vwHighReputation
WITH SCHEMABINDING
AS
SELECT  [Id],
        [DisplayName],
        [Reputation]
FROM    [dbo].[Users]
WHERE   [Reputation] > 10000

Next, since I'll be searching by display name, I've created a couple of indexes on the view:
CREATE UNIQUE CLUSTERED INDEX IX_Users_Id ON [dbo].[vwHighReputation]([Id])
GO
CREATE NONCLUSTERED INDEX IX_Users_DisplayName ON [dbo].[vwHighReputation]([DisplayName]) INCLUDE (Reputation)
GO

If I query via the view, I can see my nonclustered index is being used:
SELECT  *
FROM    [dbo].[vwHighReputation]
WHERE   [DisplayName] LIKE 'J%'

Plan: (https://www.brentozar.com/pastetheplan/?id=Sy2EoJaiv)
So far so good. I can even use my view as part of a more complex query with an OUTER APPLY, and I still get a seek with only 63 reads against my index (this is obviously a contrived example, but helps illustrate the problem that I'll come to):
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  *
                        FROM    [dbo].[vwHighReputation] AS [v]
                         WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Plan: https://www.brentozar.com/pastetheplan/?id=HJaw3y6ov
However, if I add a TOP 1 to my OUTER APPLY:
SELECT  [U].[Id],
        [A].[Reputation],
        [A].[DisplayName]
FROM    [dbo].[Users] AS [U]
        OUTER APPLY (
                        SELECT  TOP 1 *
                        FROM    [dbo].[vwHighReputation] AS [v]
                        WHERE   [v].[Id] = [U].[Id]
                    ) AS [A]
WHERE   [A].[DisplayName] LIKE 'J%';

Then the situation gets bad....very, very bad....
Plan: https://www.brentozar.com/pastetheplan/?id=HyOS6yaiw
My logical read count against that view is now almost 5 million. I can see from the plan that SQL Server is now choosing to perform a seek on the clustered index with the User's ID as the predicate, but doing so around 2.5 million times. It is also scanning the whole of the Users table. It no longer seeks on the view's index.
Obviously the optimiser is deciding that this is the most efficient approach, but I can't understand why! I've think it's probably to do with the way the underlying tables are sorted, but I'm not sure.
Incidentally, re-writing it as a simple SUB QUERY rather than CROSS APPLY yeilds the same outcome.
Any help or advice would be great!


texts: Single table or multiple tables for custom transfer queue

texts:
Currently, I'm working on synchronizing data between two SQL Server databases on separate servers but on the same network. We'll call them A(the master database) and B(the one exposed to a web application for clients). Records updated in A must also then be updated in B, whereas new records inserted in B must also then be inserted in A. While the former is never true the other way around, there could be rare cases for the latter to be true from A to B; which is not accounted for at the moment.

To solve this, I've been developing a custom queue to transfer the data. I've linked the SQL Servers, then applied triggers to the tables so that they also copy the data to a transfer queue table. Then, I have SQL Server agent run a stored procedure periodically that checks for entries in that table, to which acts accordingly and deletes the transferred data from the queue.

My question is would it be better to maintain a single transfer queue table on each SQL Server Database or multiple queue tables for each trigger? While this doesn't really apply to B as the inserted fields to transfer are always the same, the master tables in A that could get new records inserted or updated have a variable amount of fields. I'm leaning more towards a single queue and set it up so that a trigger puts each field value as a separate record with a unique key that identifies that set so that the stored procedure can group them and insert/update them in the other database all at once. Is this the better/more efficient method or should I consider multiple queues? Thanks for any and all help!


texts: Is there any way to tell the number of matches found in a database value matched with LIKE?

texts:
Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)?

EXAMPLE table, "exampleTable":

**ObjectID**        **value**
     1           I Love Lemonade
     2             I Love Love

Using a query like this one:  SELECT * FROM exampleTable WHERE value LIKE '%love%', I can easily find any entries where the value contains the word (or has a word where part of it contains the word) "love." I would like to be able to know just "how many" matches were found (for sake of search priority) in the matched fields, so that they can be displayed as more relevant. I realize that I'm probably asking a lot out of a simple query, however, I also know how powerful SQL can be, too, so thought I would ask if this was possible and if not, if there is any other approach I might take (that won't force me to rewrite the database).

I thought of using sub-queries somehow, but there is no way (that I have been able to find) to combine "LIKE" and "IN".  Also, I am not even sure using a sub-query would get the job done since I'm working with a relational database where one main table relates to 8 others and the "8 other" tables have the values I want to search on, but the main table has the location I need (although, I suppose I don't have a problem adding a "location" column to these "8 other" tables, as it would be helpful in other areas, too).

It's entirely possible that I didn't design the "relational" part of the relational database to perfection, but this is the first time I have designed (although, certainly not worked with) a relational database by myself, and, at least, it works well with the back-end of my website (yes this site is a private partial CMS).

Any help is appreciated, and thank you for your time!


texts: MYSQL item in select value where value is enum

texts:
I have some hard time with a select, i  think i used in past but i cant remember where.

SELECT * FROM tableX WHERE id in ( select ids from tableY );

in tableY i have: (ids is a string field with multiple ids separated by comma)

ids,...other fields
'1,2,3',...
'1',....

Anyone can help with a suggestion ?

Thanks a lot!


texts:
After trying with different drivers and clients, we suffer with mongodb timeout on find query.

Even Profiling logs the settings and issue well.

       2014-08-13T23:46:43.218+0200 [conn80] query mdb.fcol query: { $query: {
       unid: "39068119d2924babffedde92ef937bd0" }, $maxTimeMS: 100 } planSummary:
       IXSCAN { unid: 1 } ntoskip:0 nscanned:1 nscannedObjects:1 keyUpdates:0
       numYields:1 locks(micros) r:334 nreturned:1 reslen:341 2181ms

Though $maxTimeMS: 100, the query takes 2181ms.

How to force mongodb to respect the timeout and stop processing if time has exceeded?

MongoDB v2.6.4, 64Bit


texts: SQL Query fails when content of a single parameter change

texts:
Im very puzzled about this issue.. I have a SQL Server database with many records and it is used by my website. When the website user searches for a specific record, the SQL command gets a timeout. The only thing that is different between searches is the parameter.
Everything works within SQL Management Studio. Using newest version of SQL Server Express on a Windows Server 2016.
This fails with a timeout:
Query: SELECT * FROM dbo.v_MyView WHERE 1=@ONE AND Name LIKE @name Order by dbo.v_MyView.Date Desc OFFSET (0) ROWS FETCH NEXT (10) ROWS ONLY
Parameters: @ONE 1, @name flop%

This works:
Query: SELECT * FROM dbo.v_MyView WHERE 1=@ONE AND Name LIKE @name Order by dbo.v_MyView.Date Desc OFFSET (0) ROWS FETCH NEXT (10) ROWS ONLY
Parameters: @ONE 1, @name bandit%

Notice that it is only the content of the parameter @name that changes. searching for "flop%" fails and searching for "bandit%" works.


texts: Call to default values (MySQL) in a procedure

texts:
For the sake of an experiment, we're working with the following procedure (same DB, multiple tables):

delimiter //

CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hash_id int,
  _id_status bit(2), _id int, _criminal_status bit(1), _charge varchar(45), _plea bit(2), _sentence varchar(45), _dob int, _state_id int, _state_id_status bit(1))

begin

START TRANSACTION;

  INSERT INTO MYDB.person (`first_name`, `last_name`, `hashID`)
    VALUES (_first_name, _last_name, _hash_id);

  INSERT INTO MYDB.id_crimes (`id`, `id_status`, `criminal_status`, `charge`, `plea`, `sentence`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, LAST_INSERT_ID());

  INSERT INTO MYDB.dob_state (`state_ID`, `stateID_status`, `dob`, `hashID`)
    VALUES (_dob, _state_id, _state_id_status, LAST_INSERT_ID());

  INSERT INTO MYDB.timestamps (`create_time`, `update_time`, `hashID`)
    VALUES (_create_time, _update_time, LAST_INSERT_ID());

  COMMIT;

end//

delimiter ;

We're running into difficulties when trying to call the procedure, however. The DEFAULT and CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP values are throwing errors (call seen below). Any help would be appreciated!

call mysql.insertTables (`Debbie`, `Hopkins`, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  DEFAULT, 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1)

P.S. The hashID value is stored only as a foreign key in every table except person, so I'm not sure we need to insert it (as LAST_INSERT_ID()) in every table. The first issue mentioned is the biggest concern, though.
Thank you!


texts: To run SQL Server Profiler or to use the SQL Server Agent's Jobs function in SSMS or ...?

texts:
I have a problem here - I wish to keep track of all transaction executed on the SQL Server but was wondering how should I proceed.

If I were to use the SQL Server Profiler and configure it to store all the transaction history into a file and perform a roll-over if a specified size is hit, then I will need to make sure that the SQL Server Profiler is always open and running. Also from my understanding, it will decrease the server performance.

If I were to use the Jobs function under the SQL Server Agent in SSMS, then it become a schedule task that only run when it is being called.

So, how do I go about to keep track of all SQL transaction make to the SQL Server and record it 24/7 or until I manual stop it (and also would not slow down the performance of the server)?


texts: mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (code)" when trying to connect

texts:
I am trying to create a back up from a server by using XAMPP (via command prompt).
Unfortunately the error is: mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (10060)" when trying to connect. I have looked a bit around on StackOverflow (mostly0, but most questions are regarding "access denied for user at (...)" or they're talking about MYSQL. I have tried finding the files that MYSQL should have (which they're discussing) and looked whether XAMPP had these as well (it did not).
My question is:
What could possibly be causing this error? I know the name, password and host are correct.
How have I tried backing up the database?
I opened Command Prompt with administrator rights.
I navigated to the correct directory where mysqldump is located at.
I then typed in: C:\xampp\mysql\bin>mysqldump -e -uNAME -pPWD -hHOST DATABASENAME > C:\winhostMySqlD.sql
It does create the .sql file, but CMD crashes with the error mentioned above.


texts: How to run a mysqldump from a .txt or .sql file inside a command line window?

texts:
If I run the follow line directly in command line window it will works good.

C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql

How can I run a .txt or .sql file named testDump.txt or testDump.sql from my cmd line window?
Inside this file we will have the same line:

C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql


texts: Algorithmic order of table indexes for table operations

texts:
I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.

I am not interested in the nuts & bolts of specific implementations; I assume RDMS designers know what they are doing and they have made these as efficient as possible.

I am going to limit my discussion to a single index, I think that additional indexes simply add an additional dimension (i.e. the fundamental process has to be performed more than once).

The following assumes a single record in each case - the benefits of indexes are greatly enhanced for multiple record operations because (generally) the seek operations need to be done less times than the number of records being sought as they can be retrieved in a range.

For an unindexed table I believe the operations are:

Step                INSERT     SELECT     DELETE     UPDATE
Find the record      N/A        O(n)       O(n)       O(n)
Modify the record    O(1)       N/A        O(1)       O(1)
OVERALL              O(1)       O(n)       O(n)       O(n)

This assumes that finding the record requires a table scan but a new record is simply slammed onto the end.

To set up an index is an O(nlog(n)) operation based on an efficient sorting algorithm.

For an indexed table I believe the operations are:

Step                INSERT     SELECT     DELETE     UPDATE
Find the record      N/A      O(log(n))  O(log(n))  O(log(n))
Modify the record    O(1)       N/A        O(1)       O(1)
Update the index   O(log(n))    N/A        O(1)       O(1)
OVERALL            O(log(n))  O(log(n))  O(log(n))  O(log(n))

This assumes that finding the record is now an ordered lookup operation on the index and updating the index is a single step for DELETE and UPDATE (because you have already found the record) and an ordered lookup for INSERT

That is, inserts get worse but everything else gets better.

Is this correct?


texts: Is it possible to backup and restore part of a database in sql-server?

texts:
We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their web based file transfer application. The database is currently about 10GB however we don't need all the data - most of it is in audit tables and tables that hold calculated values that can be re-generated.

I have looked at creating a filegroup to hold the audit tables and was hoping I could just backup and restore the primary filegroup. I can backup fine but when restoring I get an error saying that I'm not restoring it to the same database. Is it possible to restore part of a database to a different server using filegroups? Is there a better way to do this?


texts: Is it possible to set named-parameters in queries SQuirreL SQL?


texts:
I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL.

This system is currently using 4 tables:

users, this table holds all the user accounts with the following columns:

ID
First name
Middle name
Last name
email
password

users_balance, this table holds all the user's balance's. The user's balance is getting updated everytime the user gets a new income or expense, after a certain date or time ofcourse. This table has the following columns:

ID
user_id
balance

user_incomes, this table holds all the incomes for the specific user. This table has the following columns:

ID
user_id
title
description
date
time

user_expenses, this table holds all the expenses for the specific user. This table has the following columns:

ID
user_id
title
description
date
time

The user can view it's current balance, but what if the user wants to check it's balance from a specific date?

Do I need to subtract and sum up all the expenses and incomes back to that specific date or do i have to create another column which keeps track of every new user balance when a new transaction is created?


texts: Sequence number shows a lower value after dump file import

texts:
I'm using Oracle 11g database in which I have more than 12 schema's consisting of many tables. I've created sequences for primary keys to these tables. I've also created synonyms to refer my application from the main schema.

My issue is,
while i try to export(using expdp) the schema's from DB in 1 server and import(impdp) the dump file to db installed in another server, sequence values  only for some tables are getting changed. Even if i try to remap the table space or schemas, the value of sequence shows a lesser value than rows in their respective tables. Due to which when i access the application it throws an error while saving the data.

I am confused and don't getting an idea to solve this. Please advise.
Is their any method to solve this issue?

Thanks in advance.


texts: Backup to local and MIRROR TO Azure Files returns ERROR

texts:
I have an Azure VM with SQL Server 2014, with a local drive (X:) for storing backups. Using Ola Hallengren's scripts. I added an Azure Files share and tried to do a mirror backup, but I get the following error:

BACKUP DATABASE [Test] TO DISK =
    N'X:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak'
MIRROR TO DISK =
    N'\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak'
WITH NO_CHECKSUM, COMPRESSION, FORMAT

Msg 3212, Level 16, State 0, Line 3
The mirror device "X:\Backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak"
and the mirror device "\\storage.file.core.windows.net\backup\SRVSQL01\Test\FULL\SRVSQL01_Test_FULL_20150420_185840.bak"
have different device specifications.

Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Outcome: Failed

Initially, the local drive was formatted with 64K allocation unit size. Tried switching to 4K, and using the BlockSize switch, but no luck. Any ideas?


texts: Constructing datetime from 2 fields: one integer and a datetime

texts:
I am looking for the most efficient way to parse 2 values; the first is stored as a datetime field with (hh:mm:ss.fff all being 0) and the second time field stored as an int with the format: hhmmssmmm). I need to take these 2 fields and convert them to a datetime field. For example:

date field = '2013-09-04'
time field = 192423998

Should return:

2013-09-04 19:24:23.997

I've seen solutions using DATEADD and CONVERT and I came up with my own (shown below) but I wonder if there's a better/more efficient way.

DECLARE @dt DATETIME = '2013-09-04'

SELECT  @dt+' '+ STUFF(STUFF(STUFF(RIGHT('0'+CAST(192423998 AS VARCHAR(9)),9)  ,7,0,'.'),5,0,':'),3,0,':')

Note: In case you wonder why the RIGHT('0'+CAST(192423998 AS VARCHAR(9)),9) - this is because a time before 10 AM will only have one digit for the hour part.


texts: Incremetal backup using mysqldump in MySQL

texts:
I wanted to take an incremental backup using mysqldump for a MySQL database. I have created a script and had taken backup using crontab. It took backup but not incremental; it only takes a full database backup. I didn't find any way to take incremental backup.

My code is given below:

backup.sh Script

#!/bin/bash
YEAR=$(date +%Y)
MONTH=$(date +%m)
DAY=$(date +%d)
HOUR=$(date +%H)
mkdir -p $YEAR/$MONTH/$DAY/$HOUR
mysqldump -uroot -pRedhat@123 mydb > $YEAR/$MONTH/$DAY/$HOUR/backup.sql

Crontab

crontab -e
# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)
30        *         *            *                1-6       exec  '/bin/bash /home/backup.sh'

Backup Files Listing

bash backup.sh

[root@master 17]# ls -lh backup.sql
-rw-r--r--. 1 root root 49M Jun 15 17:05 backup.sql

[root@master 17]# ls -lh backup.sql
-rw-r--r--. 1 root root 362M Jun 18 17:23 backup.sql

I want that it will take only changed blocks backup not full backup like above example.

Additional Information

I am using the free version of MySQL. No enterprise features available.


texts: Deleting data from some but not all tables

texts:
I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up.  I want to delete all the data from all the tables we import to, but leave the data on the other tables.  Everything of course has foreign keys/relationships/constraints set up.

First I tried manually deleting in the proper order...it was a slow process to determine the order, and the delete itself ran very slowly.
- is there a way to determine the delete order that won't run up against constraints?

I tried truncate, but that required dropping all the constraints, and I was concerned about getting them all back on without missing anything.
--is there a way to via script to drop them all then bring them all back?
Last time I ended up just creating an empty one, then bringing data over with visual studio database compare which was also slow and painful.

What's the best way?  Any tips?  I'm a .net dev that does many stored procedures but not good with the admin or DBA type stuff.


texts: What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?

texts:
As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an upgrade is a "nice to have"

In the spirit of a few earlier questions asking about reasons to prefer various newer releases of SQL Server over previous ones, what are some objective technical or business reasons a company might consider for an upgrade to SQL Server 2016 over an earlier version, even a later release such as SQL Server 2014?

(This question about SQL Server 2012 compared to SQL Server 2008, Or this one about SQL Server 2012 compared to SQL Server 2005 come to mind as examples of the spirit of this question. The answers to them also expand on some of the reasons here for companies starting on SQL Server 2008 or SQL Server 2005)


texts:
I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result.

DATETIME BETWEEN TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )  AND TO_DATE(D1-T2,'MM-dd-yyyy HH24:MI:SS' )

DATETIME BETWEEN TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )  AND TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )


texts: What is the rationale for requiring a column list when inserting into a table with identity column?

texts:
When inserting into a table with an identity column from some similar staging table, we have to set IDENTITY_INSERT to ON and specify a column list.

Why do we have to specify a column list in this case?

Wouldn't it be better to specify a change request at Microsoft Connect?


texts:
I have a client that wants to use myphpadmin to manage his mysql database 5.x on my server. I found this article on how to hide databases from myphpadmin but I don't like this because if I don't specify the databases to hide then every time I add a new database it because available to the client through myphpadmin.

Article: http://www.electrictoolbox.com/hide-databases-phpmyadmin/

Command to hide a database in the myphpadmin config.inc.php file:

$cfg['Servers'][$i]['hide_db'] = 'hidedb1|hidedb2|etc...';

I also created a separate username and password in mysql to.

Is there a way to reverse this so I can tell myphpadmin what database to only show for this login?

Thanks,

Frank G.


texts:
Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM?

Essentially we have the following:

CREATE TYPE foo AS ENUM (
  'Sometimes',
  'You',
  'Wanna',
  'Go',
  'Where Everybody Knows Your Name'
);

With a table instantiated by:

CREATE TABLE bar (
  lyrics foo DEFAULT 'Wanna'::foo
);

I'm able to determine the type of foo from the column lyrics, however, I'm having trouble finding a way to determine whether or not foo is an ENUM.

For context, I need this information to programmatically get a list of all possible values of foo when given a column of lyrics.


texts: T-SQL: How to switch all partitions from one table to another?

texts: Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?


texts: How to manage constants within an Oracle Database

texts:
This question touches on a few different parts of Oracle that I'm not particularly familiar with so bear with me:

I am trying to redesign some of the procedures and functions within the database I manage. One of the most annoying things is the use of integers to express the status of rows.

For example rows that need to be processed get the number 3, rows that have been processed get the number 0 and rows that are in the middle of processing get number 1. This is a simplified example.

What I was hoping to do was code those integers into constants so when the procedures are written the words would be self-documenting...

I've tried to use packages and functions to manage these constants. Everything comes out a bit messy. The cleanest I've found is:

CREATE OR REPLACE FUNCTION PROCESSED RETURN NUMBER AS BEGIN RETURN 0;    END;
/

This allows you to type the sql below which looks relatively neat.

SELECT rows
FROM table
WHERE status = PROCESSED;

The problem I've found is that these columns are indexed to return quickly but the functions mean the indexes aren't used.

With that background my question is: How should constants be managed in Oracle effectively? What solution has the best trade off for visual simplicity, logical organisation and database performance.


texts: How do MariaDB and MySQL differ in character set handling?

texts:
In MySQL/MariaDB, I set the connection character set to UTF-8:

SET NAMES 'utf8mb4';
SET CHARACTER SET 'utf8mb4';

There are some tables with a latin1_swedish_ci collation and latin1 encoded data in them. SELECTing from them gives me correct UTF-8 data.

When I once incorporated SET NAMES, SET CHARACTER SET in my program startup code, I assumed that I could give UTF-8 encoded data in INSERT strings to the MySQL server and it would convert it according to the table/column collation. That assumption came from me testing locally with MariaDB, where it worked. Then on the production site with MySQL: The UTF-8 data would not be converted to latin1 but saved as UTF-8 in Latin1 column. To fix that, I always encode values explicitly to latin1 when INSERTing into latin1 table.

Now, I revisited some code that did not SET NAMES, SET CHARACTER SET. I changed it to SET it. Now the MariaDB/MySQL difference strikes again. What is the difference between MariaDB and MySQL character set handling?


texts: Can this business logic be enforced by a conditional database constraint?

texts:
I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules.  This "rule" seems difficult to implement without using hacks.

CREATE TABLE CASE_STAGE
(
  ID                        NUMBER(9)           PRIMARY KEY NOT NULL,
  STAGE_ID                  NUMBER(9)           NOT NULL,
  CASE_PHASE_ID             NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  END_REASON_ID             NUMBER(9),
  PREVIOUS_CASE_STAGE_ID    NUMBER(9),
  "CURRENT"                 NUMBER(1)           NOT NULL,
  DATE_CLOSED               TIMESTAMP(6)        DEFAULT NULL
);

and

CREATE TABLE CASE_RECOMMENDATION
(
  CASE_ID                   NUMBER(9)           NOT NULL,
  RECOMMENDATION_ID         NUMBER(9)           NOT NULL,
  "ORDER"                   NUMBER(9)           NOT NULL,
  DATE_CREATED              TIMESTAMP(6)        DEFAULT CURRENT_TIMESTAMP     NOT NULL,
  CASE_STAGE_ID             NUMBER(9)           NOT NULL
);

ALTER TABLE CASE_RECOMMENDATION ADD (
  CONSTRAINT SYS_C00000
 PRIMARY KEY
 (CASE_ID, RECOMMENDATION_ID));

The business logic can be summed up as

When Inserting into CASE_STAGE
If CASE_STAGE.STAGE_ID = 1646
THEN
 CASE_STAGE.PREVIOUS_STAGE_ID must be found in CASE_RECOMMENDATION.CASE_STAGE_ID

Can this logic be embodied in a Check constraint or is an ugly trigger the only way?

Edit:

For all values of CASE_STAGE.STAGE_ID the value for PREVIOUS_STAGE_ID must be found in CASE_STAGE.ID
The application does not allow deletions from CASE_RECOMMENDATION once it is no longer CURRENT  ( ie when the value of CASE_STAGE.CURRENT is 0 this stage is closed and can no longer be changed, when = 1 this is the stage, or row, that is active and can be changed now.)

Edit: using all the excellent ideas and comments here is a working solution to this problem

CREATE MATERIALIZED VIEW LOG ON CASE_STAGE
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON CASE_RECOMMENDATION
TABLESPACE USERS
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;

CREATE MATERIALIZED VIEW CASE_RECOMMENDATION_MV REFRESH FAST ON COMMIT AS
  SELECT
         cr.ROWID cr_rowid, --necessary for fast refresh
         cs.ROWID cs_rowid, --necessary for fast refresh
         cr.case_id,
         cs.stage_id,
         cr.recommendation_id
         cr.case_stage_id,
         cs.previous_case_stage_id
  FROM   CASE_RECOMMENDATION cr,
         case_stage cs
  WHERE  cs.previous_case_stage_id = cr.case_stage_id (+)
  AND CS.PREVIOUS_CASE_STAGE_ID IS NOT NULL
  AND EXTRACT (YEAR FROM CS.DATE_CREATED) > 2010 --covers non conforming legacy data
  AND CR.RECOMMENDATION_ID IS NULL
  AND cs.stage_id =1646;
--this last line excludes everything but problem cases due to the outer join

ALTER TABLE CASE_RECOMMENDATION_MV ADD CONSTRAINT CASE_RECOMMENDATION_ck CHECK (
    (previous_case_stage_id IS NOT NULL AND case_stage_id IS NOT NULL)
);

When inserting a 1646 stage using existing packages without a recommendation the error was

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated
ORA-06512: at line 49

Job done!  Not what a materialized view was intended for but better than a trigger.


texts:
This is fresh mysql install from Debian apt.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3
..
Server version:         5.5.47-0+deb8u1-log (Debian)
Protocol version:       10
Connection:             Localhost via UNIX socket
..
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.

This is how it's set:

mysql> show global variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+

There's no sql_safe_updates setting on any config file (my.cnf or ~/.my.cnf etc.).

Why it's set to ON for my session (run mysql from root).


texts: I am using SSMS v18, I need to create an ER diagram from my database. As Microsoft removed diagram from new version of SSMS (v18) so now How can I get a diagram for my database??


texts: Cross-Domain SQL Server Service Accounts

texts:
I am working on a project in which we wish to have SQL Servers in a test domain make use of accounts in a development domain so that we don’t have to manage multiple forests of accounts.

We’ve established a one-way trust so that the test domain trusts accounts from the dev domain.  We granted ‘log in as a service’ rights to a machine in the test domain for an account on the dev domain.  When we try to install SQL Server and use that account as the service account, the install fails.

Has anyone encountered this issue and is there a resolution?


texts: If a query is executing on a multi-core server, and the query uses parallel threads, will sys.dm_exec_requests.cpu_timeshow the cpu time consumed by all threads executing the query? I assume this is the case, but I can't find documentation indicating this.


texts: Executing Child packages without using master package

texts:
I have many Fact and Dimension packages which are executed using vb script . Today one of my seniors proposed that these packages should be executed individually instead of creating a master package to execute the child pkgs using execute package task .His reasoning was based on the fact that execute package task will create several threads which will be performance intensive when compared to individually executing the packages.

Leaving aside sharing of config file and other issue how can individually executing it be more performant that creating master package for all the child packages?

By the way the child packages are executed out of process which is configured in the Execute Package Task


texts:
we have a mongodb cluster which takes about 3.7TB on the filesystem. One of our secondary nodes died, so we replaced it and started the resync.

The resync was started on 12 Jul 2017 and took until yesterday (6 Aug 2017) I can see the index rebuilding and immediately after that, I got the magical message (there are dbs before the test one):

[rsSync] initial sync cloning db: test
[rsSync] build index on: test.t1 properties: { v: 1, key: { _id: 1 }, name: "_id_", ns: "test.t1" }
[rsSync]         building index using bulk method
[rsSync] build index done.  scanned 0 total records. 0 secs
[rsSync] initial sync data copy, starting syncup
[rsSync] oplog sync 1 of 3
[ReplicationExecutor] syncing from: fr-mongo-c3-p3:27017
[rsBackgroundSync] we are too stale to use fr-mongo-c3-p3:27017 as a sync source
[ReplicationExecutor] syncing from: de-mongo-c3-b1:27017
[ReplicationExecutor] syncing from: de-mongo-c3-p1:27017
[ReplicationExecutor] syncing from: de-mongo-c3-p2:27017
[ReplicationExecutor] could not find member to sync from
[rsBackgroundSync] too stale to catch up -- entering maintenance mode
[rsBackgroundSync] our last optime : (term: 10, timestamp: Jul 12 05:29:26:14)
[rsBackgroundSync] oldest available is (term: 10, timestamp: Aug  3 06:24:29:9f0)

Now what can I do with this? First thing that comes to mind is to increase the oplog size and repeat the process. Second thing that comes to mind is to take the backup data, copy them to this new node and if this process takes less then 4 days, I might be able to make it.

Any other ideas? Or is my assumption completely wrong? Thanks for answers!

(Mongo version is 3.2.10, OS is Ubuntu Trusty)


texts: Selecting data from multiple rows into a single row multiple columns

texts:
I have a table that has columns like following. Batch number is a database design thing like row number

dm_id        type_in_id    type_student_id     amount
 36              1               1               1
 36              1               2               2
 36              1               3               3
 36              2               1               4
 36              2               2               5
 36              2               3               6
 36              3               1               7
 36              3               2               8
 36              3               3               9

I want to create a query that would return this:

dm_id  amount1 amount2 amount3 amount4 amount5 amount6 amount7 amount8 amount9
 36       1       2       3       4       5       6       7       8       9

How can I do that?

P.S. Version 5.5.56-MariaDB - MariaDB Server


texts: need to join Vertical row as per values in Horizontal rows

texts:
TABLE 1

+----+--------+-----+
| id | userId |state|
+----+--------+-----+
|  1 | 1      |AZ   |
|  2 | 1      |AK   |
|  4 | 1      |AL   |
|  5 | 1      |CO   |
|  6 | 1      |CA   |
|  7 | 2      |AZ   |
|  8 | 2      |AK   |
|  9 | 2      |AL   |
+----+--------+-----+

TABLE 2

+----+---------+--------+
| id | job  | from | to |
+----+------+------+----+
|  1 | job1 |   AZ |AK  |
|  2 | job2 |   AL |CO  |
+----+---------+--------+

I want list of Job as per their to & from both state allowed by user in mysql, For above it will return

+------+--------+
| userId | job  |
+--------+------+
|  1     | job1 | // As User 1 will have state AZ & AK
|  1     | job2 | // As User 1 will have state AL & CO
|  2     | job1 | // As User 2 will have state AZ & AK
+----+----------+

used query:

SELECT hs.userId, j.job FROM `table2` j

JOIN (
    SELECT userId,GROUP_CONCAT(CONCAT('\'', `state`, '\'' )) as stateList FROM `table1` GROUP BY userId
) hs ON  j.`to` IN (stateList) AND j.`from` IN (stateList)


texts: How to estimate disk space for a million rows while given a table with fewer rows in InnoDB

texts:
I want to estimate the disk space for 1 million rows.

Currently, I have 8527959 rows, Data Length of 744 MB and Index Length 989.4 MB.

Please let me know if my calculations are correct.

Total Data Length for 1 million rows

Data of 744MB consists of 46500 pages of 16KB blocks (InnoDB page size)

If 46500 pages consist of 8527959 rows, 1 page consists an average of 183 rows.

So, 1 million rows need (1,000,000/183) pages= 5465 pages of 16KB

So, 1 million rows of data need 87.4MB.

Total Index Length for 1 million rows

Indexes of of 989.4MB consists of 61837 pages of 16KB blocks (InnoDB page size)

If 61837 pages consist of 8527959 rows, 1 page consists an average of 138 rows.

So, 1 million rows need (1,000,000/138) pages= 7247 pages of 16KB

So, 1 million rows of data need 115.9MB.

Total disk space (approximate) for 1 million rows

Total data length+Total Index Length = 87.4MB + 115.9 MB = 203.3 MB

Is this correct?


texts: Copy database to a remote server on daily basis

texts:
I need to replace the copy of my PROD db in a DEV system on a daily basis.

The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked .bak without make changes on original DB?

Thank you


texts:
Innodb by default writes a log file named ib_logfile0, ib_logefile1 etc..

I can see that this writes are taking a lot of disk resources at my server, My question is, are this logfiles a must? or can i disable them by anyhow? or maybe I shouldn't?


texts: What does pg_stat_activity.xact_start mean for autovacuum threads?

texts:
If I see something like:

db=> SELECT datname,pid,state, (now() - query_start)::interval, query FROM pg_stat_activity where state != 'idle' and query like 'autovacuum%';
 datname |  pid  | state  |    interval     |                        query
---------+-------+--------+-----------------+-----------------------------------------------------
 theseus | 21936 | active | 00:00:00.940774 | autovacuum: VACUUM ANALYZE pg_catalog.pg_constraint
 theseus | 16775 | active | 00:33:57.896069 | autovacuum: VACUUM pg_toast.pg_toast_1234

How should I interpret xact_start vis a vis the vacuum_cost_limit and autovacuum_vacuum_cost_delay? In other words is xact_start the time at which the thread woke up again to do another chunk of vacuum_cost_limit work, or does it represent many chunks?

And a follow-up: my vacuum_cost_limit setting is the default of 200 (very conservative); if we're seeing a very long "transaction" interval and that interval should be measuring a very small amount of work, what could explain the long runtime? It's my understanding that autovacuum doesn't block on other activity.


texts: Determine if existing Trigger acts on field DB2

texts:
I am eventually going to be writing a trigger on a field named "user2". Currently most of the values in this field are blank, but there are some seemingly random rows with values of '00' for the field.

What I was wondering, is there anyway to determine if there is an existing trigger that is adding these values to the field? If not, what are some methods I can use to figure out how this data is getting there?

Thanks,

Andrew


texts: fragmented index not defragmenting after an index rebuild

texts:
I have a weird behavior that I'm trying to explain to a client with SQL express 2014 (12.0.2269.0).  The table in question has 4.5 million rows and has over 60 thousand pages.  The average fragmentation for that index is 54% before and after the rebuild.  the only way I was able to fix the issue was to drop and recreate the clustered index.

Has anyone ever witnessed this behavior?
Can it be as simple as upgrading to SP2, although I didn’t find any direct correlation with the SP1 and SP2 release notes?

Any help would be greatly appreciated.

This is the query I use to look at the average fragmentation level:

SELECT t.name AS TableName, sch.name as SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, ROW_NUMBER() OVER (PARTITION BY t.name order by t.name) as RowNum, TR.RowCnt
                FROM SYS.TABLES t
                INNER JOIN sys.schemas sch on t.schema_id=sch.schema_id
                JOIN SYS.INDEXES i ON t.object_id = i.object_id
                JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) s ON t.object_id = s.object_id AND i.index_id = s.index_id
                LEFT JOIN (SELECT o.OBJECT_ID, ddps.row_count as RowCnt
                                FROM sys.indexes AS i
                                INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
                                INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
                                AND i.index_id = ddps.index_id
                                WHERE i.index_id < 2
                                AND o.is_ms_shipped = 0) TR ON TR.object_id = t.object_id
                WHERE t.type  = 'U'
                AND s.avg_fragmentation_in_percent > 5
                AND i.name is not null
                ORDER BY t.name, s.avg_fragmentation_in_percent


texts: MySQL Replication: Slave with Different Column From Master

texts:
I have a Master/Slave replication.

I had to change the name of one of my columns in one of my tables from temp_col to sent_at, this column is not being used yet.

I've issued an alter table on an unused slave in order to change the column name with hope that once the change will finish. I would be able to use this slave (with the desired column name) as a new master.

The thing that I didn't take into consideration is that once the alter table is finished that replication will fail since the query that inserts data into the altered table expects the old column name (temp_col) even if its just inserting null values into it.

Since the alter table took some time, there are a lot of these kind of queries.

Questions

Is there something i can do to repair the replication?
How can I tell the slave to "ignore" the old column name?

thanks

Ran


texts: add index and change int data type to bigint the fastest way

texts:
How do I do this without downtime, or at least minimal downtime (less than 3 hours)?

Is there any way to tweak my.cnf to at least make this process of indexing and change data type faster?

Below is the table structure that I would like to update.

CREATE TABLE user_rew (  u_reward_id int(11) NOT NULL AUTO_INCREMENT,
                         promo_id int(11) unsigned NOT NULL,
                         user_id int(11) unsigned NOT NULL,
                         msisdn varchar(15) DEFAULT NULL,
                         reward_id int(11) unsigned NOT NULL,
                         serial_no varchar(50) NOT NULL DEFAULT '0',
                         pv_id int(11) NOT NULL DEFAULT '0',
                         is_gw tinyint(3) unsigned NOT NULL DEFAULT '1',
                         is_claimed tinyint(1) unsigned NOT NULL,
                         created_at datetime DEFAULT NULL,
                         updated_at datetime DEFAULT NULL,
                         PRIMARY KEY (u_reward_id),
                         KEY promo_id (promo_id),
                         KEY reward_id (reward_id),
                         KEY is_claimed (is_claimed),
                         KEY is_gw (is_gw),
                         KEY serial_no (serial_no),
                         KEY pv_id (pv_id ),
                         KEY user_id (user_id,msisdn) ) ENGINE=InnoDB


texts: mysql cannot find database after dropped of it and restored

texts:
Due to some errors   I decided to drop of all schema and then restore db structure from dump file.  After restoration all other mysql server clients cannot find it (error 1164 ). Only mysqlworkbench   root@localhost I can see that db.
Using same connection data @localhost other clients cannot find that db.


texts: is there a max number of rows in a SQL Server 2008/2012 and MySQL table?

texts:
Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O.

Max number of concurrent connections per instance.
Max number of records per table (if there is exist a limit).
Max number of transactions per second.

Every suggestion it will be welcome


texts:
I have a table that essentially has rows with:

sessionId
date/time
event

event can be (to make it simple, "start" and "end")

The same sessionId however can be reused, so, simply I have

sessionId     datetime                event
AAAAA         2020-01-01 11:30:00     start
AAAAA         2020-01-01 12:20:00     end
AAAAA         2020-01-01 14:22:00     start
AAAAA         2020-01-01 14:30:00     end
BBBBB         2020-01-02 14:22:00     start
BBBBB         2020-01-02 14:42:00     end
CCCCC ...etc ...

I need to convert it to:

SessionId     StartTime              duration
AAAAA         2020-01-01 11:30:00     00:50:00
AAAAA         2020-01-01 14:22:00     00:08:00
BBBBB         2020-01-02 14:22:00     00:20:00

Is there any way I can do this with an sql query or do I have to do it in code?

Thanks in advance!


texts: Microsoft SQL Server local report culture is not supported: 3072 is invalide culture identifer

texts:
I am using Windows 10, language Serbian, SQL Management Studio 2016 and can't use Database reports because there is error:

  An error occurred during local report processing  Culture is not
  supported  Parameter name: culture 3072 (0x0c00) is invalid culture
  identifier.

I try with different settings for Windows 10 - language (English), try with different settings for SSMS (English) but got same error.

Do you have idea how to solve this?

P.S. My Windows 10 system locale settings:

Get-WinSystemLocale
LCID       Name            DisplayName
----       ----            ----
9242       sr-Latin-RS     Serbian(Latin, Serbia)


texts: Creating index for large table is taking forever

texts:
I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012  Enterprise edition. I know how to create index but its taking forever and would like know how to make it faster? Here is the script i tried.

create clustered index.....on table.name ([Column.name] desc)
    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]
            go


texts:
For a particular row in a table how can I determine the number of reads done to it such as

SELECT * FROM MyTable WHERE ID = <something>

and when and where the read query executed?

I'm investigating a product that was "LOST" in a small company. So I wanted to see if anybody looked it up before it disappeared.


texts: MySQL replicate-rewrite-db ALTER TABLE not rewritten

texts:
I have a production database called "master" that I want to replicate on another server under another name (call it "slave").

All worked fine until an ALTER TABLE statement was performed on the master server. Since then, the replication hung up and I get this error in the slave log:

[ERROR] Slave SQL: Error 'Table 'master.students' doesn't exist' on query. Default database: ''. Query: 'ALTER TABLE `master`.`students`

Well of course it does not exists since I told MySQL to rename "master" to "slave"! But it seems like MySQL decided not to rename the database in ALTER TABLE statements.

Here is an excerpt from my slave config.

server-id   = 2
relay-log   = /var/log/mysql/mysql-relay-bin.log
log_bin     = /var/log/mysql/mysql-bin.log
binlog_do_db   = slave
replicate-rewrite-db="master->slave"


texts: MySQL - query join returning zero recs from table with data

texts:
I've provided a file that has the table structure and some sample data that I'm working with.

I don't understand why I'm getting nulls from a joined table, where there is data.

Here's my query

select
lt.taskListID,
lt.taskName,
ls.scriptName,
lts.taskScriptScriptID,
tpd.userTaskProgressUserID,
-- tpd.userTaskProgressTaskID,
--       tpd.userTaskProgressTaskScriptID,
tpd.userTaskProgressNote
from listTaskScripts lts
left join listTasks lt on lt.taskListID = lts.taskScriptTaskID
left join listScripts ls on ls.listScriptsID = lts.taskScriptScriptID
left join userTaskProgressDetail tpd on tpd.userTaskProgressTaskScriptID = ls.listScriptsID
where
lts.taskScriptTaskID = 2
and tpd.userTaskProgressTaskScriptID is null
--       and tpd.userTaskProgressUserID = 1
--       and tpd.userTaskProgressTaskID = 2

I'm getting nulls from tpd.userTaskProgressUserID and tpd.userTaskProgressNote, but (as you can see by looking at the pdf, there are values in those fields in that table for tpd.taskscripttaskid 2.

The query and result set are in the linked pdf.

Take a good look at the tables in the pdf. Maybe my problem lies in the table structure (I didn't create them - I'm just working with what was there...).

I hope that's enough info for someone to help me out.

Thanks

UPDATE

I made a small change based on foibs' comment and replaced the linked file with the new select and table change. Here's the new select:

select
lt.taskListID,
lt.taskName,
ls.scriptName,
lts.taskScriptScriptID,
tpd.userTaskProgressUserID,
tpd.userTaskProgressNote
from listTaskScripts lts
left join listTasks lt on lt.taskListID = lts.taskScriptTaskID
left join listScripts ls on ls.listScriptsID = lts.taskScriptScriptID
left join userTaskProgressDetail tpd on tpd.userTaskProgressTaskScriptID = ls.listScriptsID
where lts.taskScriptTaskID = 2
    and tpd.userTaskProgressUserID = 1
    and tpd.userTaskProgressPhaseID = 1
    and tpd.userTaskProgressSiteID = 2

However, I'm only getting the two records in userTaskProgressDetail. I need to list all records (script names) from listTaskScripts that are for the selected Task ID (2 in the where clause) - four records for TaskID 2. And then any notes from userTaskProgressDetail that may be for any of those records (scripts).

Do I need to change the table that I'm selecting from, then?


texts: How to make a function in postgresql with two parameters to return count from column table?

texts:
I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year.
The table is something like this:
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    status CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

I was able to return the count with the function below but when I give values to year and code in which there is no count it returns NULL but zero should be returned, how can I do that?:
CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$

DECLARE
    total INT DEFAULT 0;

BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

The data is something like:
INSERT INTO orders
(id, code, service_id, status, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);


texts:
I got an alert this evening that one of our MySQL cluster disks was over 80% full - upon checking there are lots of "on.number" files created in the last 48 hours. Can anyone tell me what these are and if they can be deleted?

They look like:

-rw-rw----   1 mysql mysql 1073742020 Feb 10 02:25 ON.002859
-rw-rw----   1 mysql mysql 1073741930 Feb 10 02:53 ON.002860
-rw-rw----   1 mysql mysql 1073742080 Feb 10 03:24 ON.002861
-rw-rw----   1 mysql mysql 1073742072 Feb 10 03:53 ON.002862
-rw-rw----   1 mysql mysql 1073742001 Feb 10 04:20 ON.002863
-rw-rw----   1 mysql mysql 1073742052 Feb 10 04:49 ON.002864
-rw-rw----   1 mysql mysql 1073741889 Feb 10 05:17 ON.002865

The most recent one looks like:

-rw-rw----   1 mysql mysql        540 Feb 10 22:18 ON.index

At this rate, the disk will fill in around 24 hours so I need to try and figure out what these are, why they are being created and if I can remove them?


texts: Why does data get corrupted in "large" text files - Reasons to use database

texts:
I am just starting to learn about databases and using mysql.  I read that databases are more reliable than having large ascii text files and that ascii text files are more prone to corruption as they get bigger.

Can someone explain why this is the case and how databases safeguard against this in a high level?  Thank you.


texts: Is it accurate to assume that MySQL's query cache gets checked on subsequent execution of queries in the form SELECT ... WHERE ... LIMIT x, y where the the only varying paramaters in the query are x and y?


texts:
I have a stored procedure that does the following:

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:

The insertion of records from the RAW table into the dbo table got rolled back; BUT
The truncation of the RAW table did not roll back.

The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.

According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?


texts: How can I keep “approximate” date data points?

texts:
I'm working on a web app that displays some products with their release date. Some dates are complete (for example, '2017-09-05') and some are not (for instance, 'first 2018 quarter', '2018 september', '2019').

The existing database is built on MySQL and I want to keep requests “simple”.
Sometimes I need to request (a) released products, sometimes (b) future products, and sometimes (c) all products.

The questions

How can I (1) retain and (2) manipulate these incomplete date data points? In the same date column? In which format?


texts:
I'm testing the MongoDB Sharding with a Sharded Collection and a forloop to insert arround 1M documents to see how splitting and moving works.

I'm surprise that after few documents MongoDB starts to split chunks (well before 64MB),

At the end of the 1M (and some) inserts i have thoses stats :

mongos> db.users.getShardDistribution()

Shard rs0 at rs0/mongod00.local.net:2000,mongod01.local.net:2001,mongod02.local.net:2002
 data : 84.76MiB docs : 1010010 chunks : 9
 estimated data per chunk : 9.41MiB
 estimated docs per chunk : 112223

Totals
 data : 84.76MiB docs : 1010010 chunks : 9
 Shard rs0 contains 100% data, 100% docs in cluster, avg obj size on shard : 88B

Why MongoDB created 9 chunks of a collection that contains 85MB?

I didn't change the defaut chunk size parameter:

mongos> db.settings.findOne()
{ "_id" : "chunksize", "value" : 64 }

Thanks,

Max.


texts: Does the existence of a college course depend on the department in which it is studied?

texts: I have a Department entity along with a Course entity, does the course entity depend on the department or is it a strong entity by itself since it has an ID that can distinguish it from other entities?


texts: Postgresql extremely slow count (with index, simple query)

texts:
I need to run these simple queries on a table with millions of rows:

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS NULL;

The count result for both queries, for project 123, is about 5M.

I have an index in place on project_id, and also another index on (project_id, trashed_at):

"index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC)
"index_subscriptions_on_project_id_and_trashed_at" btree (project_id, trashed_at DESC)

The problem is that both queries are extremely slow and take about 17s each.

These are the results of EXPLAIN ANALIZE:

      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2068127.29..2068127.30 rows=1 width=0) (actual time=17342.420..17342.420 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)
         Recheck Cond: (project_id = 123)
         Rows Removed by Index Recheck: 23746378
         Heap Blocks: exact=131205 lossy=1480411
         ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..198324.74 rows=4996823 width=0) (actual time=1582.717..1582.717 rows=4994877 loops=1)
               Index Cond: (project_id = 123)
 Planning time: 0.090 ms
 Execution time: 17344.182 ms
(9 rows)

      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2047881.69..2047881.70 rows=1 width=0) (actual time=17557.218..17557.218 rows=1 loops=1)
   ->  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)
         Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
         Rows Removed by Index Recheck: 23746273
         Heap Blocks: exact=131144 lossy=1480409
         ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..186846.55 rows=4428599 width=0) (actual time=1566.163..1566.163 rows=4994749 loops=1)
               Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
 Planning time: 0.084 ms
 Execution time: 17558.522 ms
(9 rows)

What is the problem?

What can I do to improve the performance (i.e. count in a few seconds)?


texts: Is data warehouse monitoring same as database monitoring?

texts:
I have a requirement, where data warehouse population is to be monitored. I am in confusion, and needed a clear picture. Is data warehouse different than database?

eg. For monitoring database we would monitor its availability, tablespace, cache, connections and other advanced metrics.

On what parameters can we monitor data warehouse?


texts: SQL Server MDF still has old values after update

texts:
Let me start off saying I am not a DBA.

My company is in the process of moving our SQL Server 2012 databases (recovery model of full) from one network to another.

Due to security requirements, we must transfer the .MDF and not backups.  We do not transfer any .LDF files. We also need to perform various update statements to "scrub" data that we deem sensitive. We then execute a full database backup.

After performing these steps, we must take the .MDF and process it through a file scanner to parse the file and find any words from a black list.  Now these update statements should have taken care of these words, but the file scanner finds them still.

It appears that the only way to fully "scrub" this data is to move the data into a temp table, drop and recreate the original table and re-insert the data back into the newly recreated original table.  This would involve dropping and recreating every constraint, index, and trigger each table may have.

There has to be a better way than this.  What are we perhaps doing wrong?

For clarity, the overall process sequence is:

scrubbing
checkpoint
backup
detach
filescan mdf

We don't use TDE because it is not the transferring that needs to be secure.  there is data that cannot exist on the new network (security requirements of govt. customers).

The blacklisted terms exist just in tables (not in stored procedures, functions, triggers etc.)

The file scanner is third party, so I don't know the internal mechanisms.  I know the scanner can scan various different files (text, Excel, PDF, etc.).


texts: We have deployed an Oracle RAC cluster which consists of three Oracle instances. As we are considering the HA issue, and as far as I know, the only way of Oracle HA is DataGuard. So is it possible that we deploy DataGuard in another data center as backup for the master RAC cluster and if master RAC cluster down, We can switch the DataGuard to master which enable read and write operations. Someone told me that it is a very complicated process. Is that true? how much time does this process cost? Is there any other solutions on Oracle HA or any other load balance solution to replace RAC cluster? Does Mysql have the same capability?


texts:
The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it might give me some insight on how to solve my own similar problem.

How would one design a schema in a MySQL database to hold all the activities of a specific user. In FB's case, you have many activities that a user might be able to do, like something, comment on something, add a friend, use an app, etc. All these activities have their own database schema, I'm sure, so I'm assuming that the activities log schema would need to reference these other data somehow.

So what I'm asking is for ideas. The only ideas I can come up with is to create an activities table and multiple "connecting tables", tables that reference the activities table and the activity data (e.g. comments on something, likes something, etc.). However, this schema would require a new table for every new kind of activity and new business logic to handle these new tables. Maybe one can't get around this, but I'm interested in a schema that would require very little updating to the schema and business logic when new kinds of activities are added to the system.

Just to be clear, this question isn't intended to sound like I'm just simply trying to build another Facebook clone. I only use FB as a reference to something similar to what I'm trying to do. To be more abstract about my question: I'm trying to aggregate various kinds of data in my database into a single schema that could be queried, and anytime there is a new kind of data, there should little if any schema changes, otherwise what's the point? FB has two instances of this kind of aggregation, activity logs and notifications (that I can think of).

Any help is appreciated.


texts: Kill client connection to Cassandra instance

texts:
Not able to go to find any documentation on how to kill a client connection to remote Cassandra I will instance .. as a DBA at the server end?
Seems like an easy task but why no mention of this in Cassandra documentation...

PL help
Thanks


texts: 42702 'column reference "id" is ambiguous' for RETURNING of table-returning PL/pgSQL function

texts:
This works:

CREATE OR REPLACE FUNCTION sql_fun()
RETURNS TABLE (id UUID) AS $$
  INSERT INTO foo DEFAULT VALUES
  RETURNING id
$$ LANGUAGE SQL;

SELECT *
FROM sql_fun();

This doesn't:

CREATE OR REPLACE FUNCTION plpgsql_fun()
RETURNS TABLE (id UUID) AS $$
  BEGIN
    RETURN QUERY
    INSERT INTO foo DEFAULT VALUES
    RETURNING id;
  END
$$ LANGUAGE PLpgSQL;

SELECT *
FROM plpgsql_fun();

Is this a bug in PL/pgSQL? How can I fix this while keeping the return type as is?


texts: How to control position of startup predicate in the execution plan

texts:
For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows that come from test_fn1 function. I need to control this behaviour.

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
  insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
  return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
  select s.*
    from dbo.test_fn1(@Par2,';') x
    inner join sys.objects s on s.name = x.item
    where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2
   varchar(100))
returns table
as
return (
  select s.*
    from sys.objects s
    inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
    where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)

Below is the proper plan where startup predicate makes the query does not work on any data.

Here is the plan that show wrong behaviour of placing startup predicate. In both cases we start from the same function and only order in T-SQL is changed.

Tested on SQL Server 2016 SP2.

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?


texts: Issue with one query - can some Index help to make it faster?

texts:
I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize  - so modification of the SELECT statement is not possible.
This is DB2 database.

Just to mention one important thing that query always returns 0 or 1 ROW!!!

Query is:

select *
from workorder
where parent= '682845'
and siteid= 'NY'
and woclass in ( 'WORKORDER' )
order by wosequence, taskid

Can someone please help me with definition of the proper index for this because this query is slowing the application too much and customer has big complaint.

I am not sure if I create index with three columns PARENT,SITEID,WOCLASS it will help because of the order by on which I do not have influence_
Thanks


texts: What does a green icon mean and why do the package bodies not expand when they have it?

texts:
In SQLDeveloper 4 some 10g PL/SQL packages have a regular crate while others have a green dot.

Those with the green dot do not expand their bodies (might be just a coincidence). What is the difference between the packages with and w/o a green dot and why do some packages not expand their bodies?


texts:
We're using PostgreSQL v8.2.3.

There are tables involved: EMPLOYEE and EMAILLIST.

Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)

2 tables are joined in such a way that if either EMPLOYEE.EMAIL1 or EMPLOYEE.EMAIL2 do not have a matching entry, those rows will be returned.

SELECT employee.email1, employee.email2,
        e1.email IS NOT NULL AS email1_matched, e2.email IS NOT NULL AS email2_matched
   FROM employee
   LEFT JOIN emaillist e1 ON e1.email = employee.email1
   LEFT JOIN emaillist e2 ON e2.email = employee.email2
 WHERE e1.email IS NULL OR e2.email IS NULL

Column EMAIL which is varchar(256) of EMAILLIST table is indexed.  Now, the response time is 14 seconds.

Table count statistics: Currently, EMPLOYEE has got 165,018 records & EMAILLIST has got 1,810,228 records, and both tables are expected to grow in future.

Is it a good idea/approach to index a VARCHAR column? This question immediately strike on my mind because of the reason that we've not indexed a VARCHAR column before in our application.  Experts advice/suggestion on this are highly appreciated.
With this current query and index, the response time of 14 seconds is reasonable or  is there any scope for further tuning?  What are other user's real-time experience/opinion based on this kind of table size and response time?

NOTE: My actual requirement/use case is explained in detail here.


texts: How to use MySQL trigger to reject duplicate entries?

texts:
I want to learn how to use mySQL triggers. This is an example.

my example table : exam_results

STUDENT_ID (primary)
EXAM_NAME
RESULT_1
RESULT_2
OPTION_1

I used the following example in ASP.net code to insert student exam results.

INSERT INTO exam_results (EXAM_NAME,RESULT_1,RESULT_2,OPTION_1)
VALUES (@exam_name,@result_1,@result_2,@option_1);

Now what I need to know is how to prevent duplicate EXAM_NAME for user without using UNIQUE key in mysql but with a following condition.

I want my INSERT to go through if there the trigger is not true.
Trigger to be activated before INSERT.

The trigger condition EXAM_NAME and OPTION_1 = 2 (need to fail INSERT)

CREATE DEFINER = CURRENT_USER
TRIGGER `exam_results`.`exam_results_BEFORE_INSERT`
BEFORE INSERT
ON `exam_results`
FOR EACH ROW
BEGIN
IF (EXISTS(SELECT 1
           FROM exam_results
           WHERE EXAM_NAME= NEW.EXAM_NAME
             AND OPTION_1 = (2)
          )
    ) THEN
           SIGNAL SQLSTATE VALUE '45000'
           SET MESSAGE_TEXT = 'INSERT failed due to duplicate exam name';
END IF;
END

Whats the best way I could do this ? (I know how I to do with UNIQUE_KEY but trying to learn how to do it with triggers.)


texts: We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run partitions. Any good tool to help for this task?


texts: insert into all possible combination of arrays of strings

texts:
I have a users table it has 3 columns

First Name |  Last Name  | Email

I have

first names = ["a1", "a2", "a3", "a4"]
last names = ["b1", "b2", "b3", "b4"]
email = ["e1", "e2", "e3", "e4"]

now i want to insert users table by

all combinations of the string arrays.

like below.

First Name | Last Name | Email
 a1        | b1        | e1
 a2        | b1        | e1
 a3        | b1        | e1
....
 a1        | b2        | e1
 a1        | b3        | e1
.....
 a1        | b1        | e1
 a1        | b1        | e2

in this above way of all combinations.


texts: check if found specific record then find another record with the same group

texts:
I have a table

Master table

PROBLEM_ID  PROBLEM            PGROUP
1           Other Hardware     Hardware
2           Error On hardware  Hardware
3           PM                 Hardware
4           Service            Maintenance

and a detail

TERM          PROBLEM            PROBLEM_ID
S1G166901     PM                    3
S1G166902     PM                    3
S1G166903     Service               4
S1G166902     Other on hardware     1
S1G166901     Service               4
S1G166901     Other Hardware        1
S1G166901     Error on Hardware     2

i need to query if PROBLEM_ID = '3' then find a PROBLEM that have PGROUP = 'Hardware'

like this

TERM          PROBLEM            PROBLEM_ID    PGROUP
S1G166901     PM                    3          Hardware
S1G166901     Other hardware        1          Hardware
S1G166901     Error on hardware     2          Hardware
S1G166902     PM                    3          Hardware
S1G166902     Other Hardware        1          Hardware

i try to use Outer apply but it's return too much row

my specific result is if they're not contain PROBLEM_ID = '3' then will not show but if contain will show all problem with pgroup ='Hardware' by term

i try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log AS p
CROSS APPLY (
            SELECT s.pgroup, s.problem_id
            FROM problem AS s
            WHERE   s.problem_id = p.problem_id AND PGROUP = 'Hardware'  )as s

            WHERE  exists (Select s.PGROUP,s.PROBLEM_ID
            from PROBLEM_LOG
            WHERE problem_log.PROBLEM_ID = '3'  GROUP BY term)
            ORDER BY TERM

my sequence is
- Select data from table problem_log for problem_ID = '3'

if problem_ID = '3' then For each record check found other problem (Hardware only)


texts:
Say we have two kinds of posts, the first we'll call A and the second we'll call B. Both of them share columns, such as the submission date, etc. but also have distinct ones. Posts can also be replies - they have a parent_id, which can either be null (doesn't reply to anything) or an id which refers to another post.
What I'm having trouble with is understanding the optimal solution to model such a system.
The obvious method would be to just put all of them inside a single table, which would work fine but you'd also get a ton of null values (e.g. an entry of type A will have NULL in every field of type B).
Another method, which I'm currently thinking about, would be to use inheritance, so something like this:
CREATE TABLE post (
              post_id                     SERIAL PRIMARY KEY,
              submission_date             TIMESTAMP NOT NULL,
              ...
              FOREIGN KEY (parent_id)     REFERENCES post(post_id)
)

CREATE TABLE table_A (
              ...
) INHERITS (post)

CREATE TABLE table_B (
              ...
) INHERITS (post)

The problem with this approach is that from an entry in table A I'd have no way of finding the parent_id (since it could either refer to B or A), so (to my knowledge) for every lookup I'd have to join all the tables and do it that way, which seems suboptimal.
I'm not sure if I'm missing something, any pointers to a potentially better solution would be much appreciated, thank you.


texts: Do you have to use a shared disk with AlwaysOn FCI?

texts:
Do you have to use a shared disk with AlwaysOn FCI in a 2-Node FCI? If not and if you have a 2-Node FCI and each node has a local SAN Disk can failover be automatic and will the whole instance failover automatically? My concern is the single point-of-failure with a single shared disk.

Thanks!