texts: What is the actual difference between innodb_autoinc_lock_mode 0,1,2

texts:
I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.

I see in "0" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. In this case it doesn't jump the Auto Increment numbers.

Also I see in "1" option, it locks the entire table and does the First transaction requested for N number of records say TX1. So when next transaction say TX2 is raised in meantime when first transaction is still getting uploaded using "load data", then it has to wait in the queue for the first one TX1 to complete. And then it sets the max(AI_column)+1 value from the table and does upload for the next set of load data. And then it sets the max(AI_column)+some_creepy_jump.

But I see in "2" option, it doesn't lock the entire table. Instead it keeps inserts for each process simultaneously and inserting records for which ever request falls in randomly and ends all threads with average time around (1.21 sec for  7 parellel threads using load data having 1000000 record each). In this case it has multiple transactions in mixed order. And then it sets the max(AI_column)+some_creepy_jump atlast.

I'm using mysql 5.1.61 .

My question is what is the use of option 1?
And why does it stay as default later versions of Mysql 5.1.22 ?
Have anyone comeaccross any disasters with option 2?

As my project demands multiple processes using load data for a table. And I have tested the above options and finalized to go with option 2. Kindly correct me if my experiments are wrong.


texts:
In the docs for recursive queries with WITH, you'll find this

  Recursive queries are typically used to deal with hierarchical or tree-structured data.

This seems to be true, but how does this function with RECURSIVE VIEWs? In a recursive view you can't define the edge case from the caller of the view. There is also 0-opportunity for the planner to push that edge case down..

For instance, take this query

WITH RECURSIVE t(name, groupid, parentid) AS(
  SELECT name,groupid,parentid
    FROM rl.groups
    WHERE groupid = 2 -- this defines the edge case
  UNION
  SELECT groups.name, groups.groupid, groups.parentid
    FROM rl.groups AS groups, t
    WHERE groups.groupid = t.parentid
)
SELECT * FROM t;

This will show you group 2, and all parents of that group and their parents. However, how do I make that into a useful RECURSIVE VIEW? So far as I can tell, you can't. And that eliminates the hierarchical and tree-structured data use-case. What are they good for then? Just building recursive mathematical sequences?


texts: How to change pgAdmin 4 to display rows retrieved and runtime?

texts: I've just switched from pgAdmin III to pgAdmin 4, and while overall it seems a lot slicker in terms of UI and presentation, I'm missing a few things from the previous version. In particular, I would show the execution time and number of rows retrieved at the bottom right on the results for selections. In 4, I see a small green popup that contains that information, but disappears after a few seconds. I've found the setting in File->Preferences->SQL Editor->Display called "Query info notifier timeout" that lets me keep the popup there indefinitely until clicked. I also don't see any kind of ongoing runtime display while a query is running. Is there any way to display any of this information more permanently?


texts: Which is faster among MySQL Community Server, Percona and MariaDB

texts:
I have a small setup of MySQL Community Server version 5.6 running on KVM based VPS. 4 CPU cores are available and total memory is limited to 1GB. I use MyISAM tables mainly for Wordpress and few other personal projects.

Which server can best suit my requirements among MySQL Community Server(all 5.x versions), Percona and MariaDB? I am basically looking for better performance (mainly read) and resource utilization.


texts: Is logging a good use case for a key:value document store like MongoDB?

texts:
I use Windows 2012r2 IIS for several projects.  ODBC logging works but it slows things down.  However, ODBC logging pays off when we need to audit the visits.  SQL is easy to use.

I know of no implicit relations to other tables (in an RDBMS) that I'd use.  IIS doesn't log much stock.  I use SQL to do things like see who did what, aggregate data, basic calculations.  It is one table.

The only think I can think of is if I wanted to relate it to other databases that might have information based upon a userid, for example.

Is website logging a good test case for a key:value store like MongoDB?


texts: Can someone give me feedback on my ER diagram?

texts:
I am new to creating ER diagrams and was wondering if anyone could review one that I constructed for an assignment. I used min, max notation in my design but I am not entirely sure if I did everything correctly.

Directions:

I would like to create a simple database that tracks my job history.

The database tracks my employee information: name, email, phone number, hire date (if known), and current salary. For each job it tracks the department I worked at, including the department name, manager and location.

In some cases I worked remotely, in those cases the database indicates that I did
work remotely and lists the nearest office location. Each job has a title, starting salary and an ending salary.

Because I could have multiple positions at the same company the job history portion of the database is uniquely identified by my employee id and start date. The history portion also has my end date for a role.

Assume I can only work for one department at a time and that the database tracks only one job at a time. Also assume that I want to track the information of a few of my team mates (not their salaries of course).

A single employee can have multiple job histories. A single employee can have one or more jobs. Job histories are associated with a single department.

What other assumptions might you need to simplify this example? Remember submitted ER models
should be in the standard or the (min, max) notations.

Draw an ER model that represents the above requirements description. What assumptions do you
need to make, if any, to complete the diagram? If needed make assumptions to simplify the final ER.
Bear in mind that not every app view or field discussed above is required to be represented on the ER
diagram. Additionally, in some cases the requirements may list irrelevant details that do not need to be
represented.

Here is the diagram I constructed.

Any feedback would be greatly appreciated.


texts:
I am currently setting up percona-online-schema-change to do a ALTER on a table with 300m+ rows.

I've verified that percona-online-schema-change can make the required ALTER.

The source table receives updates/inserts in large batches at erratic intervals.

During testing, with defaults values for percona-online-schema-change, the update fails due to the process not being able to obtain a lock on the new table.

My theory is that due to the erratic nature of the updates, percona-online-schema-change is not able to obtain sufficient accuracy for its chunk-size calculations, and is trying to do too much on the new table.

Therefore, I was going to set some hard values to try and obtain some more consistent behaviour.

Specifically, I was going to set a hard --check-size value and disable over-sized chunk checking.

I was also going to increase the number of retries for copy-rows from the default of 10 to 30, and increase the wait between tries from 0.25 to 10.0.

Finally, I was going to increase the innodb_lock_wait_timeout value from 1 to 5.

I know that these measures will slow down the overall operations considerably, but its more important to me that it completes in the first pass that it completes quickly.

Does this seem like a reasonable and safe approach to the update?


texts:
I have a problem with the performance of my MySQL server, let me explain:

I instaled a MySQL server, version 5.5.20

  Server version: 5.5.20-log Source distribution

SELECT queries runs fast on every table but INSERT/UPDATES queries run very slow, for example, for this table:

CREATE TABLE `sessions` (
  `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `session_expires` int(11) NOT NULL DEFAULT '0',
  `session_data` varchar(10000) COLLATE utf8_unicode_ci NOT NULL,
  `session_current_application` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'okn',
  PRIMARY KEY (`session_id`),
  KEY `INDEX_SessionExpires` (`session_expires`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I get this results:

mysql> insert into sessions values ('qweasd',1400,'data','test');
Query OK, 1 row affected (0.14 sec)

mysql> show profile;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000057 |
| checking permissions         | 0.000005 |
| Opening tables               | 0.000016 |
| System lock                  | 0.000006 |
| init                         | 0.000008 |
| update                       | 0.000045 |
| Waiting for query cache lock | 0.000003 |
| update                       | 0.000008 |
| end                          | 0.000002 |
| query end                    | 0.140482 |
| closing tables               | 0.000021 |
| freeing items                | 0.000044 |
| logging slow query           | 0.000002 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+
14 rows in set (0.00 sec)

Are this timestamps normal for a simple INSERT query?

The server has 16GB of RAM and 6 CPUs so i don't think the hardware is the problem.

What i tried:

Well, i read a lot about performance, and i check innodb_flush_log_at_trx_commit (https://stackoverflow.com/questions/6937443/query-end-step-very-long-at-random-times) i tried with 0 and 1 and times are similar, shorter with 0 but not much (from 0,13 to 0,09), and i think the insert should be faster.
Based on this thread i check my RAID but is a software RAID 1, so doesn't seem my problem comes from this.
Deactivate the query_cache, when i did it, the querys are even slower...

Query cache:

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 67108864  |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

I'm totally lost and don't know what to do... any help will be greatly appreciated

PS: This thread could be related with this one


texts: How to make variable update for each instance of for-each loop?

texts:
I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event Handler is to send an email to the client notifying them. There is a SQL task that uses an existing variable (Client ID from the file) that queries a table to get the Client's email address. I created a variable for the Client Email. I want to make this update each time the for-each loop is run, since each file will have a different value for the client email variable.
How can I ensure this?
Inside the for-each loop, there are three data flow tasks that could conceivably "error" and that's when I will be sending these notification emails. Will I need to scope a variable for each of the three executables within the loop for the Client Email? Or can I scope a single variable in the for-each loop?
I understand this may be a bit confusing so please let me know if you have any questions.


texts: How to formulate a query and view for the following structure?

texts:
Consider the following table structure:

TableA
ColumnA ColumnB ColumnC
Ro1      A         1
Ro1      A         1
Ro7      A         3
Ro2      C         1
Ro3      B         1
Ro3      B         1
Ro6      B         2
Ro4      C         2
Ro5      A         2

  Task: Create the table stipulated above with two columns (ColumnA, ColumnB), then develop a query/view that will produce the structure as shown in view above, of which ColumnC is found within a view and not within the table and its contents are automatically generated by considering changes of values in ColumnA and ColumnB respectively.

I simply don't understand how to formulate this query.


texts:
I have the following procedure (SQL Server 2008 R2):

create procedure usp_SaveCompanyUserData
    @companyId bigint,
    @userId bigint,
    @dataTable tt_CoUserdata readonly
as
begin

    set nocount, xact_abort on;

    merge CompanyUser with (holdlock) as r
    using (
        select
            @companyId as CompanyId,
            @userId as UserId,
            MyKey,
            MyValue
        from @dataTable) as newData
    on r.CompanyId = newData.CompanyId
        and r.UserId = newData.UserId
        and r.MyKey = newData.MyKey
    when not matched then
        insert (CompanyId, UserId, MyKey, MyValue) values
        (@companyId, @userId, newData.MyKey, newData.MyValue);

end;

CompanyId, UserId, MyKey form the composite key for the target table. CompanyId is a foreign key to a parent table.  Also, there is a non-clustered index on CompanyId asc, UserId asc.

It is called from many different threads, and I am consistently getting deadlocks between different processes calling this same statement. My understanding was that the "with (holdlock)" was necessary to prevent insert/update race condition errors.

I assume that two different threads are locking rows (or pages) in different orders when they are validating the constraints, and thus are deadlocking.

Is this a correct assumption?

What is the best way to resolve this situation (i.e. no deadlocks, minimum impact on multi-threaded performance)?

(If you view the image in a new tab, it is readable.  Sorry for the small size.)

There are at most 28 rows are in the @datatable.
I have traced back through the code, and I cannot see anywhere that we start a transaction here.
The foreign key is set up to cascade only on delete, and there were no deletions from the parent table.


texts: How to create an Access query to find # symbol into a field?

texts:
It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector.

I'm looking for the fields where there are # symbol but I'm getting zero results, although I have a field with # (Access query).

Data

id    obs
1     #rejected

Access Query where I do not have any results

SELECT id, obs
FROM Person
WHERE obs Like '*#*'

+info added from answers

MySQL Query works fine

SELECT id, obs
FROM Person
WHERE obs Like '%#%'

SELECT id, obs
FROM Person
WHERE obs LOCATE('#',obs) >0

Which is the escape character in Access?
Any ideas to fix Access query?


texts: Distribution database compatibility level

texts: Using SQL Server transactional replication with a distributor on 2008R2, and publishers/subscribers all on SQL 2005.  Does the compatibility level of the distribution database matter?  Should it be set to 90 to match the other servers, or is 100 fine?


texts: Can SQL Server create collisions in system generated constraint names?

texts:
I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under load:

“There is already an object named ‘PK__tablenameprefix__179E2ED8F259C33B’ in the database”

This is a system generated constraint name. It looks like a randomly generated 64-bit number. Is it possible that I am seeing collisions due to the large number of tables? Assuming I have 100 million tables, I calculate less than a 1-in-1-trillion chance of a collision when adding the next table, but that assumes a uniform distribution. Is it possible that SQL Server changed its name generation algorithm between version 2008 and 2014 to increase the odds of collision?

The other significant difference is that my 2014 instance is a clustered pair, but I am struggling to form a hypothesis for why that would generate the above error.

P.S. Yes, I know creating millions of tables is insane. This is black box 3rd party code over which I have no control. Despite the insanity, it worked in version 2008 and now doesn’t in version 2014.

Edit: on closer inspection, the generated suffix always seems to start with 179E2ED8 - meaning the random part is actually only a 32-bit number and the odds of collisions are a mere 1-in-50 every time a new table is added, which is a much closer match to the error rate I’m seeing!


texts: Design with inheritance and two 1 to many relationships to the same table

texts:
Quick schema overview:
Below is a schema being a subject of my question. In the design there are two "regular" tables questions and notes as well as two "abstract" tables:

elements, with "concrete" implementations:

video_clips, podcasts, books, articles

markers with following "implementations":

book_pages, article_pages and time_frames

What I would like to achieve:
I would like to be able to put a note or a question in an element. But along with the information that let me find it quicker, thus f.e. if book was my element, I would use a page marker (book_page). If podcast was my element I would use a minute marker (time_frame).

To give an example: Let's say I have:

a book B1 with 100 pages
a book B2 with 200 pages
a podcast P1 that lasts 8 minutes

Now I want to be able to put a note for page number 21 for the book B1 saying: Unlike many other metals, iron does not form amalgams with mercury. - f.e. because that statement drew my attention while reading the book and I want to highlight that by putting a note with that sentence, and connect it to B1 p. 21.
Let's say in podcast P1 in minute 4 I heard a sentence: Bank A has better mortgage plan than bank B because X Y Z. I want to be able to put a question related to P1 and minute 4 saying: Why X is better in bank A and not C?

Problems:
There are two important topics related to that design:

Inheritance
Two 1 to many relationships to the same table

I could get rid of the markers table, and relate book_pages with books, article_pages with articles and time_frames with both podcasts and video_clips. The problem is that I would have two FK in time_frames (let's assume it's acceptable with CHECK constraint) and three foreign keys in both notes and questions (book_page_id,article_page_id,time_frame_id) - and that is unacceptable. I've introduced markers table to solve that issue and have only one FK in notes and questions. Unfortunately that comes with high price, because now querying for an element needs twice more joins (for elements and markers).

I am not looking for the perfect solution because giving the problems there isn't one, I know I need to adopt the compromise but the question is if my compromise/design is acceptable or not ?


texts: Endpoint modification in SQL Server mirroring

texts:
I have three SQL Server 2008 R2 machines which are configure to be Principal, Mirror and Witness.

I need to move them to another subnet, therefore their IP addresses will change; and i had configure the mirror using the IPs and no their names.

My question is: if I just use ALTER ENDPOINT with the new ip address (or the name), would it keep rolling or it won't work?

I'd appreciate any advise.

Thanks!


texts: Return all possible combinations for each group

texts:
I have got a table looks like this one here shows the student ID and all classes this student took:

+-------------+
| ID | Class  |
+-------------+
| 01 |   A    |
| 01 |   B    |
| 01 |   C    |
| 02 |   A    |
| 02 |   C    |
| 02 |   D    |
| 03 |   A    |
| 03 |   B    |
+-------------+

And I would like to convert this table to the one below in order to see all possible combinations of classes for each student.

+----+---------+---------+
| ID | Class 1 | Class 2 |
+----+---------+---------+
| 01 | A       | B       |
| 01 | A       | C       |
| 01 | B       | C       |
| 02 | A       | C       |
| 02 | A       | D       |
| 02 | C       | D       |
| 03 | A       | B       |
+----+---------+---------+

Does anyone know how to get this?


texts:
I'm not sure if the title is very good, feel free to suggest a better one.

People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, an Answer or a Comment).
The way I've decided to design it is something like this:

CREATE  TABLE IF NOT EXISTS `comment` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Who voted' ,
  `object_type` VARCHAR(45) NOT NULL COMMENT 'The table or class name of the voted object. Such as \"answer\", \"question\", ...' ,
  `object_id` INT UNSIGNED NOT NULL COMMENT 'The primary key of the object being voted on' ,
  `value` TINYINT(1) NOT NULL COMMENT '+1, -1 or 0 for a comment' ,
  `content` TEXT NULL COMMENT 'A free text comment about the object' ,
  `votes` INT UNSIGNED NULL DEFAULT 0 COMMENT 'How many votes did this comment receive' ,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_vote_user` (`user_id` ASC) ,
  CONSTRAINT `fk_vote_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Keeps track of \"upvotes\" or \"downvotes\" of content.'

Probably too much info for here but basically I'm taking the approach of a generic "object_type" VARCHAR ("QUESTION" for example) and an "object_id".

Is this a good approach? Can I make foreign keys? What would you suggest to improve performance?

I am using MySQL and PHP (and Yii).


texts: Windows Logs Application is full of "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]"

texts:
I have Windows Server 2016, with SQL Server 2017, and found Windows Log Application is full of Login failed messages (as follows) :

  Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]

Client's IP addresses are various. I don't have any maintenance plan in SQL Server

What's the problem?


texts: Sharding state recovery process found document - contains wrong list of config servers

texts:
I've been stuck on this issue for the most part of the day, feel like I am hitting a wall. I hope someone more knowledgeable can help me.

Historical Setup

Mongo Shard (4x replica sets)
Config Servers (3x)
Mongos (2x)

This setup got migrated to a different DC. Process involved setting up servers in new DC, connecting them as members and subsequently shutting down instances in the original DC.

Migration got completed successfully, without any issues.

The Problem

From time to time, Mongo instance fails to start as it is trying to connect to config servers in the OLD DC.

Log line from mongod:

SHARDING [rsSync] Sharding state recovery process found document { _id: "minOpTimeRecovery", configsvrConnectionString: "config_cluster/mongo-config-1:27017,mongo-config-2:27017,mongo-config-3:27017", shardName: "shard_cluster", minOpTime: { ts: Timestamp 1536309187000|2, t: 26457 }, minOpTimeUpdaters: 1 }

Problem is, those mongo-config-1, mongo-config-2 and mongo-config-3 are domains from OLD DC. They are not in the new Mongos instance's config file.

I've found the following entries in Mongo PRIMARY's db.system.version

  { "_id" : "authSchema", "currentVersion" : 5 }

  { "_id" : "minOpTimeRecovery", "configsvrConnectionString" : "config_cluster/mongo-config-1:27017,mongo-config-2:27017,mongo-config-3:27017", "shardName" : "shard_cluster", "minOpTime" : { "ts" : Timestamp(1536309187, 2), "t" : NumberLong(26457) }, "minOpTimeUpdaters" : 1 }

My question is:

Is it safe to delete {"_id": "minOpTimeRecovery"} document?
If not, what is the safest way to resolve this?


texts: In SQL Server 2016, if we create a database in RAM (i.e using memory optimized tables), the database size should not exceed RAM size, am I right in saying that?


texts: SQL Query for searching table 'C' records only if records from table 'A' has specific values

texts:
I Have Three Tables, TBL-A, TBL-B, TBL-C, TBL-D

TBL A has records for userstatus

RECID    USRID    USERSTAT    USRNAME
1        1        1           JOHN
2        5        1           HARRY
3        6        4           GARY

TBL B has user details

RECID   USRID   FNAME  MNAME  LNAME MOBNO
1        1      JOHN   F      MOSES 1234567
2        2      MARY          MOSES 5421679
3        5      HARRY         KENEDY 1234567
4        6      GARY          MOLE   1234567

TBL C has additional details only for the user having CAT as 1(in Table A)

RECID   USRID   GRPID
1       1       2
2       5       4

TBL D has details of GRP ID

GRPID   GRPNAME GRPDETAIL
 1      group1   FIRSTGROUP
 2      group2   SECOND GROUP
 4      group4   FOURTH GROUP

On basis of user input of mobno(TBL B) all the record having mobno='xxxxx' along with corresponding records of TBL A(userstat), TBL B(FName, Mname, Lname), TBL C(GRPID)-(if existing else output as null or blank), TBL D (GRPNAME,GRPDETAILS)-(if existing else output as null or blank).
should be searched.

PLS Help


texts: MySQL indexing issue when trying to search for a part of a string/word

texts:
What we're running into:

Engine: MySQL.

We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets.

We need to search for partial words; say we have the reference AB12345678 - the user wants to find this reference by either searching AB, B123, 1234 or any other combination which contains part of this string.

We have an INDEX on reference & date

We're currently using LIKE %STRING%, but this cannot be indexed - and performance is bad in certain situations:

(1) When we search for AB we get a fast result.

(2) When we search for AB12345678 we get a slow result.

Both situations have ORDER BY date & LIMIT.

When we turn off either ORDER BY or LIMIT with situation (2), we also get a fast result.

A slow result is around 14-15 seconds of query execution time.

The dataset contains around 300k results.

What we've tried so far:

We tried implementing FULLTEXT indexes and MATCH .. AGAINST queries, however MATCH .. AGAINST doesn't allow us to search for both sides, the * wildcard is only allowed at the end of the input string.

We've also tried removing the INDEX on date. This gave us faster results (around 1/3th of the time) however, the query still took around 4-5 seconds.

We're kinda lost on the best implementation that can improve our performance on this query and how we can best solve it right now. What method should we use to get our performance back and be able to search on both sides?

Below is part our query which has slow results (we removed the SELECT part):

SELECT SQL_NO_CACHE *
FROM orders o0_
WHERE 1=1
AND o0_.customer_id = 130
AND (o0_.reference LIKE '%AB12345678%')
ORDER BY date4 DESC
LIMIT 50
OFFSET 0;


texts: SSIS - How to parse extensions from path-filename strings

texts:
I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of rows).
SSIS is not terribly helpful with string manipulation functions, and the possibilities I have are pretty complicated as well.

The derived column needs to reflect the file extensions. File extensions range from 1-10 characters (anything longer can be truncated at 10), and are (of course) separated from the file name and path by a period. They don't correspond to a concise list of file types (something like "docx, xlsx, accdb, msg" are Office types). The file path may also have periods in it. And in some cases it will not have files at the end of the path, just a folder name itself.
Some examples:

\\Server\Share\named resource
\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory
\\Server\Share\Group\Year.Month.Day.Subfolder\File.ext
\\Server\Share\Team Folder 1.404\another.file.here.extension

So I need to be able to extract the extensions up to 10 characters.
I tried TOKEN() but with folder names possibly having periods, that didn't work well - adding \ as a token delimiter helped some, but I still got odd extensions from folder names.
I can't find a combination of SUBSTRING(), RIGHT(), FINDSTRING(), and/or TOKEN() that will meet the rules and the derived column definition doesn't allow logic like IF or IIF.

Some false positives are expected and I plan to sort them out after importing. If it helps here, I have a second column in the CSVs which is extracted by SSIS, it's the size of the file (for folders it populates a 0). I haven't gotten this to matter either, because of the lack of IF or IIF in derived column definition.


texts: If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I have gathered that [tablename]_ibfk_constraintIndex is the convention for DB constraints in MySQL. Is this correct? What does IBFK stand for?


texts:
I want to take aggregate data for each sales person employed and display the Salesname, saleamount, and saleshippingamount.  I have the query set to use 2 UPDATE CTE statements to update a table that house the master data for each employee. I am updating because I need aggregate totals to be displayed.

The syntax takes roughly 10 minutes to complete and between the two tables there are roughly 60,000 rows to process.  Is a CTE The wrong way to go about this?

Below is sample DDL. The employee names are populated from the HR table, but I did not show DDL for this, just an insert statement.

Create Table #FinalData
(
    employee varchar(400)
    ,tr float
    ,tf float
)
Create Table #TR
(
    employee varchar(400)
    ,saleamount float
    ,saledate date
)
Create Table #TF
(
    employee varchar(400)
    ,saleshippingamt float
    ,saledate date
)
INSERT INTO #TR (employee, saleamount, saledate) VALUES
('Employee 1', '12.63', '2017-01-01'), ('Employee 1', '15.00' ,'2017-01-02')
,('Employee 2', '14.00', '2017-01-03'), ('Employee 2' ,'12.00', '2017-01-03')
,('Employee 3', '16.00', '2017-01-03'), ('Employee 3', '13.00', '2017-01-04')
INSERT INTO #TF (employee, saleshippingamt, saledate) VALUES
('Employee 1', '5.00', '2017-01-01'), ('Employee 1', '6.00' ,'2017-01-02')
,('Employee 2', '5.50', '2017-01-03'), ('Employee 2' ,'5.00', '2017-01-03')
,('Employee 3', '6.00', '2017-01-03'), ('Employee 3', '6.00', '2017-01-04')
INSERT INTO #FINALDATA (employee) Values
('Employee 1'), ('Employee 2'), ('Employee 3')

;With TR As
(
    SELECT
    employee
    ,SUM(saleamount) Totals
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
)
UPDATE t
SET tr = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TR
            GROUP BY employee) As t2
ON t.employee = t2.employee

;With TF As
(
    SELECT
    employee
    ,SUM(saleshippingamt) Totals
    FROM #TF
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
)
UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TF
            GROUP BY employee) As t2
ON t.employee = t2.employee

Select * FROM #FinalData

And this is my execution plan.

What is an option that I have to optimize this query?


texts: PostgreSQL: restore a record from a backup

texts:
We have a Rails app backed by a PostgreSQL DB and hosted on Heroku.

We recently had a bug that deleted some (very few before we caught it) records.

Excuse my ignorance but is there a straightforward way to directly pull a record (or 3) off of a backup and insert it back into the live DB? Or must these be recreated manually from the Rails console?

Thanks for any advice, warnings, etc.


texts: queries to active live test health of a galera cluster

texts:
What queries could be used to foresee or better fine tune alerts to check the health status of a Galera (MariaDB) cluster?

The problem I am facing is that randomly the cluster gest stalled because of a heavy DDL statement, so besides using tools like telegraf, that work great and help to get an overview of the cluster, currently I run periodically a query just writing data into a table, and if it times out I spawn an alert.

This works but is prone to create many false positives alerts, there is no configurable threshold based on X attempts, no execution time diffs between test so it can take 1 second or 10 to finish and there are no warnings, etc. so before trying to implement all the whistles and bells for alerting, and being aware that  many factors may influence, I would like to know what queries or type of them could be used periodically (every 30 seconds probably) in where the output could be a deterministic factor to trigger or not an alert.

For example a simple ping:

SELECT 1

Helps to very that serves is up and running but won't help much to check if a table is locked, etc.

Currently, I am using:

INSERT INTO table_rw (id, t) VALUES (?, ?) ON DUPLICATE KEY UPDATE t=?

Mainly to prevent filling up the table with useless data, but probably are better ways the ones that could help diagnose better without overloading to much the existing cluster.

How ideas?


texts: Where clause returns null, but need to return 0

texts:
Need an SQL Statement for the following:

Table a
  partID
  partNumber

Table b
  partID
  purchased

SELECT a.partNumber
FROM a
WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0

If the WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0 returns Null, I'll get no record to display.   Would like it to return 0 so I can show the a.partNumer having a 0 purchased quantity.

This is an MS Access database running on IIS 6.0 using ASP/VBScript


texts: How to create partition on existing table in PostgreSQL10?

texts:
How can we create partitions on the existing table which has not defined with the portion key when it is created?
I have one large table and it has 1B+ records and 600GB in size.

Are there any new approaches to create a partition on the existing table?
(OR)

Should we create a new table and copy data from old to new one?


texts: Using DATE part of datetime column in WHERE clause

texts:
I'm building a POS system, where I collect the date & time that the event took place. I would like to pull some reports by only using the DATE part of the datetime column in my SQL queries.

My current query looks like:

SELECT * FROM tbl_item_trans
WHERE  (date_time BETWEEN @start AND @end)
ORDER BY date_time DESC, code

However, if I put in two dates e.g. 14th and 15th September it only pulls data from 14. If I want to include 15th September transactions, the end date would have to be 16th September.

How can I solve this problem? It would be better also if I only used the DATE part of the datetime column.

the @start and @end variables use the date data type, e.g. '2017-09-16'.


texts: is this time to remove the databases from the availability group? which ones?

texts:
While patching one of my servers that is part of an availability group,
as a secondary, I had some problems  - the real problem was that the host machine of one of the virtual servers in the availability group - run out of disk space for a brief minute or two.

It does not matter what was the problem, for this question.

The facts are:

I had 2 machines on the availability group, lost the secondary.
The availability group was involved in replication and I was getting the following error message:

  Replicated transactions are waiting for next Log backup or for
  mirroring partner to catch up

which was solved by starting the following trace flag:

DBCC TRACEON(1448,-1)

I find some info about my availability group, and I can see one server is down according to the scripts below:

select * from sys.dm_hadr_cluster_members

SELECT
gs.primary_replica as 'Primary Server',
DB_NAME(dd.database_id) as 'Database Name',
secondary_recovery_health_desc as 'Secondary Server Health Status',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
--suspend_reason_desc as 'Suspended Reason',
--last_sent_time as 'Last Data Send Time',
--last_received_time as 'Last Data Received TIme',
--last_hardened_time as 'Last Hardened Time',
--last_redone_time as 'Last Redone Time',
--log_send_queue_size as 'Log Send Queue Size',
--log_send_rate as 'Log Send Rate',
--redo_queue_size as 'Redo Queue Size',
--redo_rate as 'Rate of Redo',
--filestream_send_rate as 'Filestream Send Rate',
last_commit_time as 'Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states as gs
left outer JOIN sys.dm_hadr_database_replica_states as dd ON gs.group_id = dd.group_id
ORDER BY gs.primary_replica DESC, DB_NAME(dd.database_id) DESC

The always On script at this question does not tell me much either

QUESTION:

How much far behind am I in the transaction log, catching up with a replica that will never come back?

I need information to solve this question:

Should I remove the databases from the availability group?

or should I build a new server, add it to the availability group, wait for the logs to catch up?


texts: Iterating through results and performing sum on values

texts:
I'm using MySQL and not really sure where to begin with this. I've got a query which returns N dates for a given company (N is likely to be 10 but could be more/less). These dates are returned as such (ddmmyyyy):
SELECT id, dates FROM myTable WHERE id='123'

123,01012016

123,13012016

etc...

I'd like to find information from another table that hs similar dates and get values within a boundary. For example, Table2 has

ID, DATE, AGE
123,01012016, 3
123,03012016, 7
123,15012016, 10

I'd like to SUM all Ages between each date brought back by the first command. In this example, the first command boundary would be 01/01/2016 - 13/01/2016 so I'd like to sum all values in the second table that fall between that bounday. This would loop for all date boundaries returned in the first query.

I don't really have a starting point for this type of query in MySQL so any help or pointers in what I should be reading docs for is appreciated

Thanks


texts:
I have a field defined as Question VARCHAR(1024) CHARACTER SET utf8 NOT NULL. Our application stored double-encoded data into this field. For example, é shows up as Ã© in phpMyAdmin, and C383C2A9 when passed to HEX.

I've added a second field (DoubleEncoded TINYINT NOT NULL DEFAULT 1), which identifies which records suffer from this problem.

I want to fix the remaining fields. Is there a way to do this using MySQL SQL? In other words, I need something to replace decode_utf8 in the following:

UPDATE `MyTable`
   SET `Question` = decode_utf8(`Question`),
       `DoubleEncoded` = 0
 WHERE `DoubleEncoded` = 1


texts: How many tempdb data files required for this

texts: you have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.you scale up the virtual machine has 16 vCPUSs and 64 GB of memory. You need to provide the lowest latency for tempdb. What is the total number of data files that tempdb should contain?


texts: PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

texts: PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks


texts:
I have an employee table in two different servers with the same structure. I want to shift all the data to main server but there are some records already exists in main server that are duplicate of local one's but are incomplete, now i want to compare these records so i can chose the most completed one and delete the incomplete one from one of the servers. surely i will use dblink to compare but what would be the SQL query for it?

in short some of the records are incomplete in local server while some of them are incomplete in main. is there any way to compare them and chose the completest one?

Thank you in advance.


texts: SSDT Not Dropping all Foreign Keys before Drop Table

texts:
SSDT V11.1.50730.0
Using Schema compare to make changes from my development environment to a test environment (a copy of Production).

Part of the changes is the addition of columns to an existing table [Items]. The script that SSDT creates first drops foreign keys, creates and populates a temp [Items] table, then attempts to drop [Items] table. This produces the error:

Could not drop object 'dbo.Items' because it is referenced by a FOREIGN KEY constraint.

Is there some setting or reason that would result in the script generator to not include dropping and later recreating some of the foreign key constraints? Just wondering if it is something simple before I start manually modifying the script.


texts:
While setting up the MySQL replication, I took the mysqldump (without --opt --routines options) unaware of the fact that in mysqldump, --routines it is NOT enabled by default.
Questions

Is there any way to just insert the routines into the slave without disturbing the replication?

Do I have to do the whole thing again by taking a new dump with --routines option?


texts:
I have a table with ~100M records of unique urls VARCHAR(1023) like /t/some_page_name_..., an average length is 51 chars.

I'm trying to select the best way to apply a unique index on the table, so I need to calculate by which column unique index will have the smallest size:

VARCHAR(1023) (url) - data size ~52 bytes, index size - ??? bytes
VARCHAR(64) (SHA2(url, 256)) - data size - 65 bytes, index size - ??? bytes
BINARY(32) (UNHEX(SHA2(url, 256))) - data size - 32 bytes, index size - ??? bytes

Can you help me?
Thanks


texts:
I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another stored procedure that deletes values from the LU tables and then reseeds the ID column directly after.
Stored procedure code
Create Procedure DBA.ReseedIdentity

@TableName nvarchar(20) = NULL,
@IdentityColumn nvarchar(25) = NULL

AS BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

IF (@TableName is null or @IdentityColumn is null)
    BEGIN
        RAISERROR('You Must Specify a Table AND the ID Column from that Table to Reseed the Identity!', 16,1)
        RETURN;
    END

Declare @MaxID int
set @MaxID = (SELECT ISNULL(MAX(@IdentityColumn),0) FROM @TableName)
DBCC CHECKIDENT(@TableName, RESEED, @MaxID)

END

I wanted to know if there is any trick to be able to pass the @TableName parameter following the FROM clause without dynamically creating it. I am new to writing stored procedures, so please forgive me if the answer is obvious.
Any help is greatly appreciated.


texts:
I tried opening my PostgreSQL database and I am getting following error:

  An error has occurred:
  could not connect to server: No buffer space
  available (0x00002747/10055)   is the server running on "localhost"
  (::1) and accepting TCP/IP connections on port 5432?   could not
  connect to server : no buffer space avalaible (0x00002747/10055)   is
  the server running on "localhost" (::1) and accepting TCP/IP
  connections on port 5432?

I have installed PostgreSQL in D drive. I am using PHP on Windows.

How can I solve this problem?

Update:

My PostgreSQL problem was solved the problem how means I installed the PostgreSQL in D drive so, I seriously I checked C drive space there was less memory so, I uninstalled some applications where installed in C drive after restarting my PC automatically started. But one thing we should remember where ever we install the applications like C drive, D drive and so on.. but that application acquire the C drive buffer space so, in C drive minimum space is required memory is up to 5 GB then we won't get any application error.


texts: Why does SSAS keep restarting every few seconds?

texts:
We have a SQL Server 2005 Analysis Server which began restarting this weekend, seemingly without any external changes.

The current state of the server is that as long as the SSAS service is enabled, it will keep restarting. If the service is manually disabled and then stopped, the service will no longer cycle. But as soon as it is enabled, the service will begin to cycle again.

Internet searching finds a few examples of this behavior, but without a resolution which seems applicable. See, e.g.: Service Level Accounts - SQL 2005 SSAS - Error connecting to OLAP. See also, e.g.: I Can’t Connect to Analysis Services in SSMS...

Brent Ozar reported a similar problem on LessThanDot, but there was no resolution on the thread and the last entry was at Fri Mar 27, 2009 4:08 pm.

This is a snippet of the msmdsrv.log file:

(10/6/2014 3:21:43 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:21:43 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:22:40 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:22:45 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:22:45 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:23:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:23:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:23:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:24:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:24:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:24:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)

The event viewer for system log, over and over, has:

"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
etc....

There are no SQLDmprNNNN.mdmp files which seem to be created. There is no other error logging that we are aware of.

How can we find out what's wrong? Is there a way we can tell if any third party services or processes are cycling the SSAS service?

We have also posted this question on SQL Server Central.


texts:
I have a script that connects to my local mongos and do some processing. However, during the process, I encountered many cursor timeout exceptions, which (I suspected) left many dead connections on my server.

When I run mongostat on my host, I see 450+ connections:

insert  query update delete getmore command  vsize    res faults  netIn netOut  conn repl       time
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:07
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:08
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:09
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:10

How can I make sure that these are idle resources that I should free up? If they are, how can I free them?


texts: Sum of decimal values that come in a string

texts:
Is there an easy way to get the sum of all decimal values in a string column.

Example:

"ABC $3.25, DEF $2.50, HIJ2 $0.25"
"ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50"

I want to add only the values after the $ and before a ,.

Any help is highly appreciated.


texts: Tail log backup query in log shipping reversal

texts:
I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query:

Script to backup Log:----------

DECLARE @filepath VARCHAR(200)
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn'
BACKUP LOG [DBNAME]
TO DISK = @filePath WITH NO_TRUNCATE, NOFORMAT, INIT,
NAME = N'DBNAME-Tail Log Backup',
SKIP, NOUNLOAD, NORECOVERY, STATS = 10

And script for Log restore is:

Script to restore Log:--------

DECLARE @filepath VARCHAR(200)
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn'
RESTORE LOG [DBNAME]
FROM DISK = @filepath WITH RECOVERY;
GO

But after making secondary database writable, my applications are not connecting to the database. While, trying backup and Restore from GUI works well. Please Help!


texts: Is there a way to avoid the password prompt in pg_basebackup?

texts:
I'm attempting to get a server provisioned using ansible, meaning that I can't type in a password. I'm doing a master-slave topology and I need to run pg_basebackup on the slave machine to accomplish this. I've looked into using .pgpass, but that appears to be for my shell user, not the postgres account.

Is this possible? Or will I have to make a manual stage in my deployment?


texts:
After configuring two Service Broker instances that successfully send msg to remote server on separate databases, I am trying to set up a second service broker instance on one of the same, successful, databases.

I am receiving errors on ssbdiagnose, only. No other tools show errors.
2 Errors from ssbdiagnose, when running on the source server.

'target IP' 'targetDatabaseName' The certificate 'source cert name' used by user '' for dialog security is not deployed in the database.
'target IP' 'targetDatabaseName' The user '' from sourceDatabase '' on 'source IP' cannot be mapped into this database using certificates.

The source cert name from the first error msg is my previously existing certificate on the source server that is used to send msg to a different database on the target server.

So it appears to be looking at the wrong certificate.

When running ssbdiagnose on the target server, I get the following error:

Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication.

Thank you


texts:
I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue.

I know this is very abstract question, but still if you can assists with what exactly the issue here

For example:

select id
from table
where year < 2010

Against

select id
from table
where year <= 2009


texts: Mysql: Possible to increase the priority of your query on a database?

texts: In mySQL is it possible to increase the priority of your query when competing with other users without caching your query before hand?


texts:
For the AdventureWorks sample database query below:

SELECT
    P.ProductID,
    CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
    SELECT TOP (1)
        TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
    ORDER BY
        TH.TransactionID DESC
) AS CA;

The execution plan shows an Estimated Operator Cost of 0.0850383 (93%) for the Index Seek:

The cost is independent of the cardinality estimation model in use.

It is not a simple addition of the Estimated CPU Cost and Estimated I/O Cost. Neither is it the cost for one execution of the Index Seek multiplied by the Estimated Number of Executions.

How is this cost number arrived at?


texts: Associating large numbers of Items with large numbers of Users in Mongo

texts:
Say I have tables Items and Users, which are both in the order of millions. Users can be associated with rows in Items via a 1 to many relationship. This is expected to be on average, a few thousand. What is the best way to go about this? I can only think of 2 ways to do it:

Create a separate table linking the 2 up. This could end up having billions of rows, so I have space concerns here.
Embed the item id's in each User, but then i'd be often pulling out more than I need, and inserting/deleting ID's would also be inefficient.

It seems like it would be a common scenario, so am wondering what kind of solutions are out there.


texts: Breakout Query Results By Year And Month

texts:
I have two tables that house data and I need to perform calculations on both sets of data, so I thought a Union All query would be best.  Well that gets me the result set that I am after, but now i need to take this a step further and provide a breakout by year/month for the data.  So the equation I am after is (vramt-saleamt)/vramt and this is my DDL which provides the data and a date, but how would I split the data out to be a breakdown by month/year

Desired Output

Jan 16 Feb 16 Mar 16
XXXX   XXX    XXX

And this is sample DDL

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')

SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
saledate As saledate
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
UNION ALL
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
saledate as saledate
FROM @Store1 t2
Group By t2.cardname, t2.saledate


texts: How to upgrade SQL Server 2016 and 2017 Standard to 2017 Enterprise edition

texts:
We have one SQL Server 2016 Standard instance, and two SQL Server 2017 Standard instances installed on the same server. Now my management wants to install SQL Server 2017 Enterprise edition and upgrade all three instances to SQL Server 2017 Enterprise.

Please suggest the right procedure to completed this whole process.

I'm new to SQL Server DBA tasks, and I need some suggestions on how to upgrade SQL Server 2016/2017 Standard to SQL Server 2017 Enterprise.


texts: Restoring SQL_ASCII dumps to a UTF8-encoded database

texts:
I've got a Postgres 8.4 environment where the encoding on all our databases is set to SQL_ASCII - We're finally migrating to Postgres 9.2, and I would like to migrate everything over to UTF8 encoding.

Unfortunately the text data in this DB is not clean -- Trying to restore the pg_dump to a utf8-encoded database throws errors about invalid byte sequences, even if I specify --encoding=UTF8 when I run pg_dump (presumably because Postgres doesn't know what to make of them and just dumps them unchanged?).

We have a LOT of data (upwards of a million rows with text/string elements), and auditing all of it by hand would be very time consuming (and error prone) so I'd like to automate this if possible.

Is there an easy way to find the non-utf8-conforming strings/text fields in the database so we can fix them?  Or am I stuck with a manual audit to straighten this mess out?


texts: Populating one foreign key based on entry of another foreign key in Access 2016

texts:
I currently have three tables as shown below:

The user enters Certificates via a form. I want CertCounty to populate based on selection of TownshipID. I've attempted the following Data Macro:

But, it is giving me this error:

I'm pretty sure my expression for the Value is incorrect as it was a stab in the dark and I had little confidence it was correct. I'm also open to other solutions, i.e. VBA, just not sure what to do as I'm still learning how to work with databases and, in particular, Access.


texts: set up proper role/group acces on databases and schemes

texts:
On my postgresql server I'm struggeling / finding the best practice for a proper role/group setup.

I have a number of clients on one machine; each client has its own role: client1; client2;client3 etcetc Depending on what kind of application they have, the role is added to a group, which corresponds with the database: app1; app2

-- Add clients to the application
GRANT group_app1 TO client1;
GRANT group_app1 TO client4;

-- Grant access to the database app1 to the corresponding group
GRANT ALL ON DATABASE app1 TO group_app1;

Each database doesn't have a public schema; only schema's with the same name as the role are made so they are used as the standard search path. But off course i don't want to give access to other schemes but their own one.

-- Grant access to client1 to the schema
-- ** how to revoke access to other members of the group? ** --
GRANT ALL ON SCHEMA client1 TO client1;
GRANT ALL ON SCHEMA client4 TO client4;

Two questions:
1 - How can I now make sure the different users cannot access the other scheme's in the database?  I tried REVOKE ALL ON SCHEMA scheme_name TO group_name. But that results in the following error:

 Fout in query: ERROR: syntax error at or near "TO"
 LINE 1: REVOKE ALL ON SCHEMA client1 TO group_app1

2 - How can I tweak this setup? Or is this o.k.?

Database list

* Database1 (Owner: group_beheer)
    + Client1 (scheme)
    + Client4 (scheme)
* Database2 (Owner: group_beheer)
    + Client2 (scheme)
    + Client3 (scheme)

Roles

* Client1
* Client2
* Client3
* Client4
* group_app1
    + Client1
    + Client4
* group_app2
    + Client3
    + Client2
* group_beheer
    + dbadmin


texts: How are Transaction IDs compared in Postgres?

texts:
I'm trying to understand how Transaction IDs are compared in Postgres

I have a two questions

Transaction IDs (XIDs) are represented in 32 bits. Therefore there can be a maximum of 4,294,967,296 XIDs. Why is a wraparound possible at two billion XIDs?
How are XIDs compared?

Consider that there are only 3 bits to represent XIDs allowing the following values for XID - 0,1,2,3,4,5,6,7.

Before a wraparound XIDs can be simply compared x < y.

However after a wraparound, say the last XID was 7 and the next XID would be 1. How will these be compared to establish that XID 1 is greater than XID 7 (to allow visibility of the row with xmin as 7)

In my research I came across the following but I'm at a loss how the comparison works

From the Postgres docs

  Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there are two billion XIDs that are “older” and two billion that are “newer”; another way to say it is that the normal XID space is circular with no endpoint

From a post by Tom Lane

  Ordered comparisons on XIDs are no longer simply "x < y", but need to be expressed as a macro. We consider x < y if (y - x) % WRAPLIMIT < WRAPLIMIT/2.
  This comparison will work as long as the range of interesting XIDs
  never exceeds WRAPLIMIT/2


texts: Remove all instances of a character from a column within tag

texts:
I don't know much about writing SQL and a client of mine has an erroneous semicolon appearing in his database that he'd like removed. It's in 1 specific column, but it should only be removed if it is within an  tag. For example:

MyColumn
--------

This is okay; but  is not

Can this be done?


texts: Insert Into table Exec SP with bad performance

texts:
I am working on a datawarehouse. One of our staging tables that is refreshed every night has about 10 million rows. We are using a custom built ETL tool that I can't make too many changes to. The tool loads this staging table like this:

truncate stage_table;
insert into stage_table with (tablockx) (column1, column2, etc...)
exec load_stage_table @batch_id = @batch_input

The contents of load_stage_table has some setup and a select statement. I can't share the exact code, but here is a basic example.

create table load_stage_table
(
     @batch_id varchar(max) = null
)
as

-- <update batch_id in batch_table>

-- collect data

select
    column1 = table1.column1,
    column2 = table2.column2,
    ...
from table1
join table2
    on table2.id = table1.table2_id
-- many more similar joins

The problem is that when I run the stored procedure as its meant to be run with our ETL tool, the run time is almost 30 minutes. However if I modify the stored procedure to have the insert statement on the inside, then it only takes 1 minute.

    create table load_stage_table
(
     @batch_id varchar(max) = null
)
as

-- <update batch_id in batch_table>

-- collect data
insert into stage_table with (tablockx) (column1, column2, etc...)
select
    column1 = table1.column1,
    column2 = table2.column2,
    ...
from table1
join table2
    on table2.id = table1.table2_id
-- many more similar joins

After running this a few times both ways and examining the execution plans, it seems that parallelism is not used when the insert is outside the stored procedure.

Does loading the table from the return, outside of the stored procedure, prevent parallelism? Or is this an indicator that the select statement needs some query tuning?


texts: Is it possible to assign a grant to a table for all users, or a default permission so that when new users are created they will have the specific grants for that table to SELECT, UPDATE, INSERT and DELETE?


texts:
I am just trying to figure some ways to do manage archiving of some/most of our Application data within the database and wondering if something like this would be possible:

Archive anything with a status of resolved, and a data updated over 3 years
Move MOST, but not all that data to the archive database, and replace the values within the current production database with a “SymLink” that points to the archived database?

Here is a simplified example:

#######################################################################################################
###                                         Active_Prod                                             ###
#######################################################################################################
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  <SYMLINKED to Archive_Prod.pzpvstream>    #
#                                                                                #
#                                                                                #
#######################################################################################################
###                                     Archive_Prod                                                ###
#######################################################################################################
#  ALIASAPPTYPE     ALIASAPPREASON  PZINSKEY                PZPVSTREAM                                #
#  App_Type_1234    New Enrollee    132387Something6357997  [BLOB Data]                               #
#######################################################################################################

So the query for select * from Active_Prod would return the following results:

ALIASAPPTYPE    ALIASAPPREASON  PZINSKEY                PZPVSTREAM
App_Type_1234   New Enrollee    132387Something6357997  [BLOB Data]

We would not be concerned with updating or inserting data as the Archive_Prod database would be set to read only anyway.
My thinking here is we could drastically reduce the Active DB2 instance by archiving most of the date (The bulk of the data resides in the BLOB anyway), but keep the “Key” fields in the “Active” database for speedier lookups.
But by creating a symlink of the data, we can improve the performance of PEGA, and improve the backup/restore times by dramatically reducing the overall size of the database.


texts: SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?

texts:
PostGIS provides a function called ST_MakeLine(pt1,pt2). It's great for constructing lines. MySQL does the same with LineString(pt1,pt2). What do you call the SQL Server analog that takes two Points and constructs a LineString?

I looked up LineString, but didn't see it mentioned.

Here is a test case on dbfiddle.

Sample data,

CREATE TABLE #tmp (
  pt1 geometry,
  pt2 geometry,
);

INSERT INTO #tmp(pt1,pt2) VALUES
  (geometry::Point(1,1,4326), geometry::Point(2,2,4326)),
  (geometry::Point(2,2,4326), geometry::Point(5,5,4326)),
  (geometry::Point(3,3,4326), geometry::Point(4,4,4326)),
  (geometry::Point(4,4,4326), geometry::Point(3,3,4326)),
  (geometry::Point(5,5,4326), geometry::Point(4,4,4326));

Query

SELECT pt1, pt2
  'My Line' AS line -- what goes here
FROM #tmp;

And I'm wanting "My Line" to be the LineString.

pt1         pt2         line
POINT (1 1) POINT (2 2) My Line
POINT (2 2) POINT (5 5) My Line
POINT (3 3) POINT (4 4) My Line
POINT (4 4) POINT (3 3) My Line
POINT (5 5) POINT (4 4) My Line


texts: How to change username to connect to server in pgadmin4 in query tool?

texts:
Back in pgadmin 3, I could change the username in the query tool by clicking on postgres on postgres@localhost:5432 dropdown:

However, I don't see this option in pgadmin4. How do I change the user from postgres to some other user? Also, is there a way to set a user other than postgres as the default user in PGadmin 4?


texts: Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request?

texts:
I am getting this error :
"Error 102 : non-boolean type specified in a context where a condition is expected"
for this request :

DECLARE @num_dossiers TABLE (num_dossier INT,indice NVARCHAR(3))
insert into @num_dossiers
select num_dossier,indice from dossier where num_sec=57

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

The request triggering the error is the last one :

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

What could be the cause of this error ?


texts: Replication of PostgreSQL cluster from Datacenter to Office with write access

texts:
Some information about the cluster:

  Version: postgres 9.5

  Size: /postgres/data$ du -hs . 854G

  OS: Ubuntu 14.04 LTS

  Storage: Raid10 SSD's

I have a master-slave PostgreSQL 9.5 cluster running in a datacenter using standard postgres replication features (streaming replication and wal-shipping). My goal is to build a second "slave" server in our office to be able to reproduce bugs that occured in production. As postgres hot-standby slaves only support readonly queries, my approach would be to nightly recreate the slave and start it stand-alone with write access. The data from last night would be sufficient for 95% of the support tickets and I am fully aware that all changes to these databases made during the day would be lost.. ;)

As the cluster is quite large, I'm not sure what would be the best way to implement this.

I'm thinking about a cronjob running nightly on the office slave:

master_datacenter: pg_start_backup
rsync postgres data directory from slave_live_datacenter to slave_lastnight_office
master_center: pg_stop_backup();
rsync wal files from slave_live_datacenter to slave_lastnight_office
start slave_lastnight_office (in live writable mode, not slave/recovery mode)

Would it be possible to skip the rsync part and only update slave_lastnight_office to the state of last night with the newer wal and pitr? I guess not, as the changes during the day would make the replaying of the new wal impossible?


texts: MongoDB replica set quorum config (network partitioning behaviour)

texts:
I use mongodb 4.0.10. I want to establish a quorum for a cluster of one primary node and two secondary nodes as written here. When the number of nodes is less than the quorum, 3 nodes in my case, cluster goes to readonly (no election).

I`ve tried to set priority of two nodes to 0, in this case if primary goes down, there is no election, but if one of secondaries goes down, old primary still exists.

According to MongoDB docs terminology is it possible to set a replica set Fault Tolerance to zero? It means that if any of cluster nodes goes down new primary will not be elected.

UPDATE
rs.conf():

rs0:PRIMARY> rs.conf()
{
        "_id" : "rs0",
        "version" : 4,
        "protocolVersion" : NumberLong(1),
        "writeConcernMajorityJournalDefault" : true,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "mongo0:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "mongo1:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 0
                },
                {
                        "_id" : 2,
                        "host" : "mongo2:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 0
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : -1,
                "catchUpTakeoverDelayMillis" : 30000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5cf5011183ea2fa5beade86b")
        }
}

I want to set a quorum rule to 3. It`s a split-brain protection measure. It means that if no 3 alive nodes in the cluster there is no able to write, only to read. I set priority of secondary nodes to 0, it means that if primary fails - no new primary will be elected. It works fine, but I also want to configure such primary node behaviour: if any secondary nodes are not available in the cluster - primary change status to secondary till all cluster members will be available again.


texts: Schema design for products with redundant values

texts:
so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the scheme goes as follows:

Product table.

Product

  id    product
------ ------------
   0    Nike Shirt

Color table

Color

  id    color
------  -------
   0    black
   1    white
   2    blue

Size table

Size

  id   size
------ -----
   0    XS
   1    S
   2    M
   3    L
   4    XL
   5    XXL

Stock table

Stock

  id    product       color    size   quantity
---------------------------------------------
   0        0           0        2       5
   1        0           1        3      10
   2        0           2        2       2
   3        0           2        4       3

This works perfectly well, my issue is: some times a product(e.g. black and white patterned T-shirt) can be categorized as black as well as white!! so we will end up with multiple records in Stock table for the same product! -see tables below for more clearance-

Product

  id    product
------ ------------
   0    patterned T-Shirt

Color

  id    color
------  -------
   0    black
   1    white

Stock

  id    product       color    size   quantity
---------------------------------------------
   0        0           0        2       5
   1        0           1        2       5

This will causes table Stock to be inaccurate in terms of quantity, I need the product to have more than one color if need for sorting purposes in the UI(user-end/Front-end) and I also want to keep track of the quantity of each color along with the sizes.

I am thinking of creating a separate table that includes the product along with all colors that it can be categorized in. this table will be meant for sorting purposes only. this is a great solution except that it will require more data-entry.

Is there any other way around?!


texts: Improving query time for auto complete box with millions of record

texts:
I have a very basic requirement. I have a table that store city id (primary key) and city name (nvarchar value). The remaining information is stored in a separate table. In my UI, I have an auto complete box, that lists all cities when user start typing. After selection, application gets the data for that particular city to fill up.

The main table with just two columns for city id and name, is indexed on name. I am currently running following query to get the list of names.

Select cityname from citylist where cityname like '%' + citynamevar + '%'

This query is supposed to give me the list of all names that contain the value typed by user. It currently takes about 2 minutes to run on approx. 600k records.

I have also added a way to display 10 records in the beginning, while the query executes. But even then, it is not helping the case as remaining data loads way too slow.

I am trying to find ways I can improve this query time. Till now I have found about full text search, which I believe should help. I was just wondering if there is any other method I can try or any trick to get the query to respond fast enough.

I am currently using SQL server 2012. I tried running the query directly outside the application, using ssms too. And even then it took the same amount of time. Indexing sped up the query by about 20% but not enough to be meaningful.

Edit:

No idea why I am not able to comment anymore on this post. Anyway, it is a test build to see how much impact auto complete boxes will have. It is running on my personal laptop (core i5, basic mobile GPU). I am using SQL server 2012, .net 4.5 with wpf.

I can ask user to reduce the search space by entering country name. But this is supposed to be a generic solution. In some cases, we don't have any way to reduce the search space. And it has to be a "contains" search, instead of "starts with". Which might be the reason why indexing hardly had any impact.

The table is just 600k records, out of which we need to display 1000-10k. The query is executed only once when user type the third character, after which remaining characters are sorted within the application from that 1000-10k records. I can reduce the impact by increasing the character count to 5, but then user will be unable to see any suggestions for most city names.


texts: postgres :: FATAL: could not access file "pglogical": No such file or directory

texts:
We installed pglogical from source code, we are running PostgreSQL 9.4

 make USE_PGXS=1 clean all
 make USE_PGXS=1 install

Installation was successful after updating the parameters, we tried restarting PostgreSQL and it responded with the above fatal error:

$ echo "include 'pglogical.conf'" >> $PGDATA/postgresql.conf
$ echo "wal_level = 'logical'" >> $PGDATA/pglogical.conf
$ echo "max_worker_processes = 10" >> $PGDATA/pglogical.conf
$ echo "max_replication_slots = 10" >> $PGDATA/pglogical.conf
$ echo "max_wal_senders = 10" >> $PGDATA/pglogical.conf
$ echo "shared_preload_libraries = 'pglogical'" >> $PGDATA/pglogical.conf

Basically I am trying to setup replication between 9.4 and 10, I was able to create the pglogical extension on 10.


texts: placing conditions in a join query join on vs where

texts:
Is there and advantage to one of the following options over the other or are they the same ?

select *
from T1
Join T2
   on T2.id=T1.t2_id
   and T2.field=value;

vs.

select *
from T1
Join T2
  on T2.id=T1.t2_id
where T2.field=value;

And in general - are there performance (dis)advantages to placing the conditions within the JOIN ON statement as opposed to the WHERE statement ?


texts: Postgres ignoring index on join, but all planner estimates are correct?

texts:
I've got a query that looks like this:

SELECT *
FROM (
    SELECT *
    FROM (VALUES ('abc123')) AS user_ids(user_id)
) AS user_ids(user_id)
LEFT JOIN users ON users.id = user_id

But instead of using the index on users.id, the query planner opts for a seq scan on the ~2.5M row users table:

                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Right Join  (cost=0.03..400966.32 rows=1 width=1065)
   Hash Cond: ((users.id)::text = ('abc123'::text))
   ->  Seq Scan on users  (cost=0.00..391452.66 rows=2536966 width=1033) <-- HERE
   ->  Hash  (cost=0.02..0.02 rows=1 width=32)
         ->  Result  (cost=0.00..0.01 rows=1 width=32)

Notice that the estimates (ie, on the size of the users table, and that exactly one row will be returned) are correct.

Also, Postgres will use the index on users.id (a unique primary key index) for other queries:

=> EXPLAIN SELECT *
-> FROM users
-> WHERE id IN ('abc123');
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((id)::citext = 'abc123'::citext)


texts: E11000 duplicate key error While Adding Shard

texts:
I have setup a replica set and want to add it in a mongos. But I got below error in mongos:

mongos> sh.addShard("rs3/172.19.0.12:27017,172.19.0.6:27017,172.19.0.5:27017")
{
    "code" : 11000,
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: admin.system.version index: _id_ dup key: { : \"shardIdentity\" }"
}

from the above message, it says admin.system.version has duplicate key error. The collection admin.system.version should be set by mongodb. I don't understand why it has a duplicate key.

All mongo instances are 3.4.4 version.

Below is the replica set status:

rs3:PRIMARY> rs.status()
{
    "set" : "rs3",
    "date" : ISODate("2017-07-09T02:13:37.146Z"),
    "myState" : 1,
    "term" : NumberLong(10),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "optimes" : {
        "lastCommittedOpTime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "appliedOpTime" : {
            "ts" : Timestamp(1499566407, 1),
            "t" : NumberLong(10)
        },
        "durableOpTime" : {
            "ts" : Timestamp(1499563905, 1),
            "t" : NumberLong(9)
        }
    },
    "members" : [
        {
            "_id" : 0,
            "name" : "172.19.0.12:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 2493,
            "optime" : {
                "ts" : Timestamp(1499566407, 1),
                "t" : NumberLong(10)
            },
            "optimeDate" : ISODate("2017-07-09T02:13:27Z"),
            "electionTime" : Timestamp(1499563936, 1),
            "electionDate" : ISODate("2017-07-09T01:32:16Z"),
            "configVersion" : 414750,
            "self" : true
        },
        {
            "_id" : 1,
            "name" : "172.19.0.5:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 1403,
            "optime" : {
                "ts" : Timestamp(1499566407, 1),
                "t" : NumberLong(10)
            },
            "optimeDurable" : {
                "ts" : Timestamp(1499563905, 1),
                "t" : NumberLong(9)
            },
            "optimeDate" : ISODate("2017-07-09T02:13:27Z"),
            "optimeDurableDate" : ISODate("2017-07-09T01:31:45Z"),
            "lastHeartbeat" : ISODate("2017-07-09T02:13:35.870Z"),
            "lastHeartbeatRecv" : ISODate("2017-07-09T02:13:35.854Z"),
            "pingMs" : NumberLong(0),
            "syncingTo" : "172.19.0.12:27017",
            "configVersion" : 414750
        },
        {
            "_id" : 2,
            "name" : "172.19.0.6:27017",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 2487,
            "optime" : {
                "ts" : Timestamp(1499070510, 1000),
                "t" : NumberLong(3)
            },
            "optimeDurable" : {
                "ts" : Timestamp(1499070510, 1000),
                "t" : NumberLong(3)
            },
            "optimeDate" : ISODate("2017-07-03T08:28:30Z"),
            "optimeDurableDate" : ISODate("2017-07-03T08:28:30Z"),
            "lastHeartbeat" : ISODate("2017-07-09T02:13:35.865Z"),
            "lastHeartbeatRecv" : ISODate("2017-07-09T02:13:36.965Z"),
            "pingMs" : NumberLong(0),
            "configVersion" : 414750
        }
    ],
    "ok" : 1
}


texts:
I was studying "Partition Function" and "Partition Schema" in SQL-Server in MSDN. I found this syntax that maps all partitions to the same filegroup.
For example:

CREATE PARTITION FUNCTION myRangePF3 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS3
AS PARTITION myRangePF3
ALL TO ( test1fg );

My question is that what is the key point in doing this?
Is this even called partitioning???
When do we usually map all partitions to the same filegroup?


texts: Non-programmer using Sequel Ace, how to resolve "Out of sort memory, consider increasing server sort buffer size" error?

texts:
I don't know MySQL but I wanted to glance through some data in a table. I imported the database into Sequel Ace (MacOS) and had no trouble browsing through the tables, but when attempting to sort one column in a larger table I received an "Out of sort memory, consider increasing server sort buffer size" error.
How do I make that happen?
I have MySQL installed and running.


texts: Why is my DR AG database in the "recovery pending" state?

texts:
I am trying to build an architecture where we get automatic failover for both HA and DR for our DBs using SQL Server 2016 AlwaysOn Availability Groups.  When I tested this using only one Availability Group, the DR DB said it was in a 'recovery pending state'.

Do I need to put the same DB on two Availability Groups to avoid the Quorum problem (one for HA and one for DR)?

Is it possible to have one DB on 2 AGs (one AG for a AG Cluster and one only for DR)?


texts: Mysql load from infile stuck waiting on hard drive

texts:
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk loads all happen within a single transaction, all keys are disabled beforehand, and each csv file is only a few megabytes large.

The problem I've run into is that the test machine gets IO bound. It has enough ram available (12G) to hold the entire transaction in memory and do a single flush out the other end. As far as I understand the manual, the innodb tables shouldn't touch the hard drive until it flushes the dirty pages at transaction completion.

The total data to be loaded is about 1G, spread across the different tables. It ends up taking 37 minutes to load it all. Here's my current test settings for perusal. I'd also be happy to report the results from show engine innodb status or similar queries if necessary.

To recap, I need to know if 37 minutes is a fast insert speed for this data size, and what I can do to increase the insert speed.

Edit:

Whoops! I forgot some important info.

Mysql version 5.5
Server has 12G total ram
Total rows inserted ~2,597,240


texts:
I have to move a database from one cloud provider to another. It has to be done in an hour window. The DB is 16Gb, and the line speed between the current host, and Azure (The final destination) equates to a 30 hour backup file copy. Far exceeding the time we have.

I wanted to do transnational replication, and once the main data volume is replicated over a few days, simply switch the website to use the replicated database, and kill the source.

Unfortunately, the database we are inheriting has numerous issues. Views are broken (Reference deleted tables/columns). And a lot of columns have no primary keys. (Please note, not my database. Well, it is now.. but..)

So, replication fails to even start because of the views, and then won't replicate tables with no PK.

My other option would to be do backups, and incremental backups. But do incremental backups split into separate files? If so, I could backup to a folder on the source, then move the large initial backup, and then move the incremental updates. Is that an option? Will incremental backups give me smaller files? I'd then be able to move the files over a period, and then on go live day, run the final backup, and move the small incremental file.


texts: same field in bothe "where" and "order by" - how to index for this query?

texts:
The query:

EXPLAIN  EXTENDED
SELECT *, u.user_id
FROM xgate_geoloc g
  JOIN tig_users u
  ON g.uid = u.uid
WHERE
         -- corners selection:
          ( ( (lat BETWEEN COALESCE(?, lat) and COALESCE(?, lat))
          AND (lat NOT BETWEEN COALESCE(?, lat) and COALESCE(?, lat))
            )
         OR ( (lon BETWEEN COALESCE(?, lon) and COALESCE(?, lon))
          AND (lon NOT BETWEEN COALESCE(?, lon) and COALESCE(?, lon))
            )
          )
     AND sex = COALESCE(?, sex)
     AND language = COALESCE(?, language)
     AND city = COALESCE(?, city)
     AND province = COALESCE(?, province)
     AND country = COALESCE(?, country)
     AND time BETWEEN COALESCE(?, time) and COALESCE(?, time)
ORDER BY time DESC

does that "OR" in the where expression have any effect on te index I should be using ?
'time' field is used in both where and order by. Any way to forge an index in this scenario ?
order by time desc - descending order; yet another bummer

Thanks for the input!


texts: SQL Server transaction replication ignores DML which does not change data

texts:
I have a transactional replication setup between the two servers and I noticed that if a run a statement similar to this:

UPDATE mytable
SET mycolumn = mycolumn

the replication somehow knows to ignore this transaction and it does not get applied on the subscriber. I have confirmed it by running SQL Profiler and also by adding TIMESTAMP column to my subscriber table (it does not change).  I suspect there is some sort of mechanism, which enables this kind of "smart" behavior and I was wondering if anybody could shed some light on it.

Thank you!


texts: What is a Scalable Storage Mechanism for large TTL data collections

texts:
We currently have a legacy webservice that stores each xml request/response in Sql Server. The data only needs to persist for 3 days before it is considered expired. Sql Server is not good at deleting rows since every delete forms part of the transaction log. The db currently grows at 6-10gb per day and this is going to increase. Only around 1% of the responses that are stored are ever recalled therefore this is a very write heavy application. Each request/response xml document can be upto 14k in size.

What storage mechanism would you choose for upto 50/100gb of data per day?

I understand the solution is not sustainable and I am really looking for a tactical fix since we cannot easily change how all our clients query and re-query the data. We could look into a db that has native support for TTL (Riak, Postgres) etc or maybe a file/blob s3/azure storage solution is a better fit? The issue with a cloud blob storage solution could be lookup performance if we had to scan multiple buckets (since buckets have capacity limits) especially compared to the current sql server single table lookup.

Open to ideas and suggestions?


texts: Why is SQL Server memory showing in task manager

texts:
I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager.

However I have recently been doing some testing on SQL Server that someone else has setup.

I am expecting to see sqlservr.exe running at about 40mb memory. However, Task manager is displaying that over 4Gb of memory is being used.

Is there a setting in SQL Server that is causing the task manager to report this inaccurately? Or is there something going wrong in the background of my SQL Server that is causing it to balloon?


texts: Setup a PostgreSQL HA with synchronous replication in windows

texts:
I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same setup in Recovery Center. planning to setup postgres in streaming mode. I am focusing on DB replication model.What kind of replication should I have between the 2 servers ? Is something synchronous feasible.

I did try the replication process with hot standby mode in postgres, But have some confusions which needs some guidance.

Configuration Steps: https://pastebin.com/7DJAvnPr

*Consideration:

DB is successfully configured in streaming mode "hot standby" (Primary) DC server --> (Secondary) DR Server

Network failure switching to secondary server will be done automatically during permanent failures FROM DC to DR

Network Switching back will be done automatically from DR to DC

OS : Windows server , DB : Postgres 9.6

Normal Operation:

With hot standby, the replication is working as expected and DB is getting synced as expected.

Operation live in secondary Server:

Since the DB is already in sync state all my required info are intact with my DC.

since the postegres in setup in slave mode,which is read-only in my case. We were not able to write data in DB.

  Would like to understand is there any internal PostgreSQL mechanism
  available for making this DB as master

or

  Should i reset the posgres configuration to master and restart postgres service

Recovered state (switching back from DR server to DC server):

I am not able to visualize what should be done. and how should we proceed with the DB sync from secondary to primary. Would like some advise here

** I know I got Windows machines and I can make a Windows Cluster(that postgresql listener I wanted) and It should failover automatically between the postgresql windows services in case one stops.


texts: Maintenance Plan folder missing in SQL Managed Instance (via VM)

texts:
Accessing our new SQL Managed Instance via an Azure VM/SSMS, the Maintenance Plan folder is missing from the Object Explorer pane.  Reading other posts (that all relate to other versions of SQL Server) I've checked I have the valid permissions:

Is there a way to enable maintenance plans in SQLMI?
If not (assuming that SQLMI doesn't use maintenance plans), what is the modern day Azure equivalent to perform automated backups/deletion/index maintenance?


texts: Oracle - Is it possible for the results of a transaction not to be visible to a subsequent transaction?

texts:
We're having a strange problem with a flaky integration test. This is using Java/JDBC against an Oracle 12 database. The test does this, in pseudocode:

transaction 1 {
    insert into order (id, cancelled) values (123, 0)
    insert into report (id, cancelled) values (234, 0)
    commit
}

transaction 2 {
    update order set cancelled = 1 where id = 123
    update report set cancelled = 1 where id = 234
    commit
}

transaction 3 {
    select cancelled from order where id = 123
    if cancelled <> 1 then fail test
}

transaction 4 {
    select cancelled from report where id = 234
    # this check sometimes fails:
    if cancelled <> 1 then fail test
}

All of that one by one in this order, on the same thread. The test usually succeeds, but sometimes (once in 10 to 20 runs) it fails on the last check.

Note that when this happens a manual database inspection reveals that the value of cancelled is in fact 1, so the problem is not that the code has malfunctioned; the problem is that transaction 4 is somehow not seeing that new value.

One theory I'm looking into is that somehow transaction 3 is already seeing the results of transaction 2, but transaction 4 is not. Is that possible? At what point are the results of a transaction guaranteed to be available to subsequent transactions? We use connection pooling; what if transaction 4 uses a different connection? What are other directions we could investigate for this occasional test failure?

Many thanks in advance!


texts: SQL Server Alerts (notify operator when job failed)

texts:
I setup database mail, created an operator today and enable notify operator when job failed.

Is that all or do I need to create an alert too?

My boss just needs emails if any job failed.

Also how to test it to see it will work in the future?

Thanks


texts: using update query with subquery in Oracle sql

texts:
I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work.  Below I get multiple values that come back from the select statement but I think my syntax is incorrect below.

UPDATE sku@bn2 x
INNER JOIN (
   select g.prev_perm_ret, g.itm_cd
   from gm_prc_reg_prc_items g, sku@bn2 b
   where g.itm_cd=b.stylecode
   and b.storecode=00000
   and g.prev_perm_ret<>b.listprice
   and g.prev_perm_ret>g.ret_prc
   and b.sellprice=b.listprice
) y ON x.stylecode=g.itm_cd
SET x.listprice=y.prev_perm_ret


texts: BCP export more rows per batch to file (queryout)

texts:
I am using Windows 10 and running this straight from the command prompt (Administrator). Is there a way to change the row count being output to a text file?

BCP "SELECT * FROM db.dbo.table WHERE row_date = '2016-10-11'" queryout "C:/Users/table.csv" -t, -c -S "servername" -T

1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000

12136 rows copied.
Network packet size (bytes): 4096

Then I tried -b 5000 and -b10000:

BCP "SELECT * FROM db.dbo.table WHERE row_date = '2016-10-11'" queryout "C:/Users/table.csv" -t, -c -b 5000 -S "servername" -T

But it still outputs 1000 rows. I tried to increase the packet size with -a but that didn't impact it at all.


texts: Using Ola Hallengren’s Maintenance in SQL Server 2016 with TDE Enabled databases and compression

texts: I’m using Ola Hallengren’s maintenance and backup solution and looking into leveraging TDE compression as I have all the databases in instance using TDE  impression. Is there a way of getting benefit of TDE compression with Ola’s solution?


texts:
I'm attempting to add a Web Reference to my Visual Studio Web API to link my existing SSRS server to said API. I'm on the Add Web Reference page and I enter the proper URL and the webpage appears in the view window but for some reason it's not finding any services.

Is there something special I have to do in order to enable this functionality?