How to get count 0 using group by if record not exist in the same table?

This should accomplish what you're looking for but this assumes all of your eCodes exist at least for one record in your Test table. Otherwise you can replace the Test table instance on the right side of the CROSS JOIN in the CTE below with your source table of eCodes:
WITH CTE_EmployeeECodes AS -- Gets a distinct list of all Employee and eCode combinations
(
   SELECT DISTINCT E.Employee, EC.eCode
   FROM Test AS E
   CROSS JOIN Test AS EC
)

SELECT EEC.Employee, EEC.eCode, SUM(CASE WHEN T.eCode IS NULL THEN 0 ELSE 1) END AS cnt -- Sum up the number of eCodes per Employee, counting NULLs as 0
FROM CTE_EmployeeECodes AS EEC
LEFT JOIN Test AS T -- Left join allows us to keep all records from our source Employee & eCode combinations CTE, but T.eCode will be NULL for the eCodes an employee has 0 instances of
    ON EEC.Employee = T.Employee
    AND EEC.eCode = T.eCode
Group By EEC.Employee, EEC.eCode
Order by EEC.Employee, EEC.eCode


SQL Server setting up logins for backup site

I was wondering whether I should transfer the NT Service,NT AUTHORITY and ###MS****### logins from one server to the other

No need to transfer those logins, because they are created automatically when you install new SQL Server instance
By the way, easiest way to transfer other logins from one server to another, is to use sp_help_revlogin procedure:
How to transfer logins and passwords between instances of SQL Server


Installed mysql 8 on Mac then mysql 5.7 now now they wont work

If you originally installed mysql from mysql.com, then the installer adds a "mysql" section in your system preferences which has an "uninstall" button there.


High latency with SQL Server 2014 Synchronous AlwaysOn

Turning my comment into an answer.

There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have been called something better).

Let me correct some of the assumptions in the thought process in the original question.

  2.Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.

Transaction Delay (ms)/Sec, in a vacuum, means only that you have at least two synchronous commit replicas. This is because ONLY synchronous commit replicas have this value as we wait for the acknowledgements from the other synchronous replica that the data is hardened.

In order to have a complete picture and get an AVERAGE of how much latency overhead PER TRANSACTION we will need to capture the Mirrored Transactions/Sec counter. This counter will give us how many synchronous commit transactions were "mirrored" to other synchronous replicas. If we take the Transaction Delay (ms)/Sec and divide it by the Mirrored Transactions/Sec counter the end result will be the average delay in ms per transaction.

  3.Using AG1 with Async we have 0 latency, but that is something we can't use.

This is not accurate at all. Ther, in fact, is GREATER latency is getting the transaction blocks hardened on the asynchronous commit replicas it's just that we don't wait for them to be hardened like we do with synchronous. Thus, there is still a delay (your network doesn't magically become a non-wait environment) it's just not counted in that specific counter as that counter ONLY contains information for synchronous commit replicas and transactions.

  6.During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.

There is not nearly enough information to make me believe this is the case. Even on some pretty terrible infrastructure I normally see 200-300 ms and nothing near 1 second PER individual transaction.

  7.When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

See my response to 3. which is also the same answer to this.

Follow Up

Capture both counters, Transaction Delay (ms)/Sec AND Mirrored Transactions/Sec and post back the results.
Test against your specific workload as the change in workload types will greatly factor in to response times.


Does it make sense to upgrade to a newer PostgreSQL JDBC driver?

You should always ensure that the JDBC driver is at least as new as the back-end server.

PostgreSQL's catalogs change between major versions, and other details like the bytea quoting method, string escaping features, etc have also changed over time. PgJDBC contains compatibility code to cope with this by querying the server version. If you don't use a current driver, you may have issues with some features, particularly JDBC metadata access and bytea fields.


<meta charset="UTF-8"> -- correct for outside MySQL

utf8mb4 is the term you need to use inside MySQL, including

mysqli_set_charset($myConnection, "utf8mb4");

Ã£ is Mojibake for ã.  This happens when latin1 is mistakenly somewhere in the data flow.

Hex EA E7 E3 is the the latin1 encoding for êçã.
Hex C3AA C3A7 C3A3 is the UTF-8 (utf8mb4) encoding for them.

Since you have presented different symptoms, let me present to you 'Best Practice' and how to diagnose the symptoms for 'black diamond with question mark', 'Mojibake', etc.  Note that there are 4-5 places where you have to consistently use UTF-8.


There are a few issues with the Tech05_03_TestingInsertionInTempTable.sql file. It uses a function called [dbo].[ConcatenateWithOrder] which isn't defined. I think I understood your intention and adjusted it to produce a few insert statements.

To answer your first question, it is not true that SQL Server cannot use read-ahead reads to get sql_variant data. If I run DBCC DROPCLEANBUFFERS; immediately before the insert I get the following output from statistics io:

  Table 'SQLVariant01_60Cols'. Scan count 1, logical reads 66311, physical reads 3, read-ahead reads 66307, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To answer your second question about the estimated row size in the plan, SQL Server does not look at the space used by the table and divide by the number of rows. Instead the query plan contains an estimation based on the datatypes in the table. For more details read this post by Martin Smith.

SQL Server estimates your table to have a row size of 235 KB. I can get close to that:

  240484.0 = 0.5 * (8016 * 60 + 8)

That's a little off from 235 KB but I don't know how the space estimate for sql_variants are calculated exactly. But you can see that you are likely to get overestimates when using a datatype that can store many different datatypes. For the record I don't think that estimate has anything to do with the performance issue that you observed.

To answer the question that I think you wanted to ask (why is the query with sql_variant so much slower than other one?), I tested it a bit and there appears to be a large CPU overhead when selecting from the sql_variant table. I selected the rows instead of inserting them (discarding them before they reached the client) and found a significant difference in cpu time. The sql_variant query had a CPU time of 3151 ms and a total elapsed time of 3268 ms. The other query had a CPU time of 686 ms and a total elapsed time of 744 ms. The difference in CPU time almost fully explains the difference in run time.

It doesn't seem unreasonable for there to be an overhead when working with sql_variant columns, right? I don't know of any tricks to avoid paying the overhead. If you feel that your data model needs sql_variant columns I can only recommend testing to make sure that you can get good enough performance.


Return 2 lines from a table if one line contains a column > 0 and the other is NULL

select t1.*
from the_table t1
where t1.location_id in (1,2)
  and exists (select *
              from the_table t2
              where t1.item_id = t2.item_id
                and t2.quantity > 0
                and t2.location_id in (1,2));

The condition t2.quantity > 0 will be false for null values, so if all quantities for an item_id are null the where condition will be false, and the corresponding item will not be returned

Online example: http://rextester.com/NSRQ98002


Are innodb_large_prefix and innodb_file_format settings backwards compatible?

First of all, never trust what a random internet person tells you about your environment; the only way to be sure is by doing your own testing. It is very difficult to give an absolute correct answer without knowing all the details of your environement.

Answering broadly your question "Is it safe to enable barracuda and large_prefix_index?" the answer is yes, it is safe and backwards compatible.

barracuda is a new file format that allows 2 extra row formats: Dynamic and Compressed. In general, the internal row format is transparent for the application, although some new features may require the new formats (large_prefix_index and optimized blob storage). Both of those features are enabled by default on the newest MySQL and MariaDB versions, which normally only happens for mature and backwards compatible options. In my personal opinion, there not a reason to use a legacy format on the latest server versions unless you are maintaining older installations.

Note that older data doesn't automatically get converted to the new format, that is why if something is wrong, you can just return to the old options and nothing will have changed. To get advantage of the new format you will need to create new tables or rebuild those after the options change (they are just "defaults", they don't automatically change anything on the tables).

In particular, large_prefix_index allows for larger indexes, which is usually necessary to allow for full UTF-8 compatibility. In particular, I have seen many applications fail because they require the utf8mb4 character set, which require up to 4 bytes per character, and not having that option enabled makes table creation fail. That is likely your case.

"Do I need to check for my mySQL db version prior to changing this?" Indeed you do, older versions of MySQL/MariaDB don't support that option. Newer versions don't have the option available (so they may fail to boot if provided such options) because the option is hardcoded to the new value. In general, MySQL supports older formats, but warns you to rebuild them when running mysql_upgrade if they are considered incompatible.

Is there any scenario where that can fail?- I can see it failing in very very specific scenarios where redundant or compact row formats are expected, but those should be rare- in my opinion all applications that are maintained should be compatible with barracuda/compressed row formats (Antelope and file formats in general are deprecated). If you have legacy applications that require a very special mysql confuguration consider keeping an older mysql installation or not rebuilding the tables to the new format, but I would consider that a huge technical debt and not best practices.


Is it possible to do an in-place upgrade of SQL Server 2005 Standard to SQL 2012 Enterprise?

Yes, according to Supported Version and Edition Upgrades you can upgrade in place from 2005 Standard to 2012 Enterprise, provided SQL Server 2005 is currently on an operating system supported by SQL Server 2012 Enterprise (meaning forget about XP or 2003 RTM) and that the architecture is the same (e.g. 64-bit -> 64-bit).

Though you should do step 1 and 2 anyway so you have something to compare to and fall back on, since you never know what might go wrong during the upgrade. (And don't forget to knock on wood and all that.)

Personally I prefer a side-by-side installation onto a new machine, especially if the 2005 hardware and/or operating system is old. The performance benefits you get "for free" when you upgrade are amplified immensely when you also upgrade the underlying stuff as part of the process.


mongo2.6 local file size on secondary is much larger than primary

Your secondary node have more (free) disk space than primary! If you don't specify your oplog size in config file (or command line parameter), mongod will take 5% of free disk space (at dbpath) for opLog.


Removing older components after upgrading

You could uninstall them, but why? They're just sitting their idle not hurting anything, but you could get over-zealous and remove something you didn't realize you need.

I'd be more concerned about the underlying OS - has it been upgraded along the way too? We just had a scenario where we couldn't upgrade to 2016 because the OS was no longer supported by SQL Server.

IMHO: Just leave those old components be.

If some pointy-haired security person automatically thinks that "old" == "insecure" without any evidence, then there are probably some components you can remove without causing problems, and some that involve more risk.

Safe (and I'll list those alphabetically):

old versions of upgrade advisor - current one, even, after you've upgraded.

Not slam dunks:

setup support files (until everything involving that version has been cleanly removed)
older management objects (on a system I have with 2014, 2016, and v.Next, I only have 2014 management objects listed)
CLR types for older versions (I would ensure that all of your databases are in at least that version+1 compatibility level and that you don't have custom CLR objects that rely on that version)
Integration Services (how do you know these aren't being used?)

For things that are services (like SSIS), a less risky way to do it would be to turn the services off for some period of time (a full business cycle is ideal). If nobody complains that the service isn't up throughout that time, than it is possibly safe to take the next step. As a bonus, your security folks should be happy in the meantime because if the service isn't running, it isn't a security risk.

At some point it makes more sense to just back up all your databases, pave a new OS, install a brand new instance fresh, and blow the old system away. This Chicken Little approach to "cleanup" for imaginary security risks just doesn't sound like a good time to me, and in fact seems to involve more potential risk than any security concerns they could possibly have.


Should roles without login have the connect permission on a database?

It is fine to grant CONNECT on a database to a NOLOGIN role. The effect is that all members of the role are allowed to connect to the database. That is independent from the question whether the role can login or not.
Note that by default, everybody (PUBLIC) is allowed to connect to a database. You would have to REVOKE that privilege to make the above privilege meaningful.


Questionnaire database design - which way is better?

Definitely do not hard code your questionnaire. Use a relational database or xml files.  I propose the following tables

Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.
Section: The sections a questionnaire is made up. Number of the section, section title, description.
Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).
Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.
Respondent: The persons answering the questions. Personal data, user number.
Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).
Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).
Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.


Possible to restore a damaged database's PRIMARY filegroup only?

Since the database is ONLINE, you have two main options:

Create a new database with the same schema for [PRIMARY] filegroup objects as the old database, and transfer data using any of the tools available (e.g. SSIS, bcp, linked server). Depending on the complexity of the existing schema, constraints, and relationships (e.g. foreign keys) this may be quite time-consuming.
Take a partial backup of the [PRIMARY] filegroup only. Restore this to a new database and files using piecemeal restore. Finally, mark the filegroup as defunct. Be sure DBCC CHECKFILEGROUP for [PRIMARY] runs cleanly first. You should export the schema and data to a safe place as in option 1 as well. As you know, having tested backups of your valuable data is priority #1 :)


Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

If you include ID I would assume that you can join it back to itself on ID to get that information in one dataset.


The following is a script I wrote for displaying the failover and failback history:

SELECT d.name, t3.time, (1 - t3.role) as status
FROM (
    SELECT t1.database_id, t1.time, t1.role
    FROM
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time
            FROM msdb.dbo.dbm_monitor_data dbmd
        ) as t1
        INNER JOIN
        (
            SELECT RANK() OVER (ORDER BY database_id, time) AS rec, database_id, role, time
            FROM msdb.dbo.dbm_monitor_data dbmd
        ) t2 ON T1.database_id = T2.database_id and t1.rec = (t2.rec - 1) AND t1.role <> t2.role
    ) t3
    INNER JOIN sys.databases d ON t3.database_id = d.database_id
ORDER BY d.name, t3.time

I believe Role = 0 is PRIMARY.


If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed?

>> NOTE FROM O.P.: While I have accepted this answer, please be aware that it is not conclusive since extensive testing has not been done. It is possible that certain types of updates, or series of updates, might have slightly different requirements, etc. But for the moment, there is no evidence or information suggesting any different behavior. <<

This testing confirms that you can delete the contents of the Log and Update Cache within your Setup Bootstrap log folder and still be able to apply future CUs, GDRs, and SPs.

Phase 1:

As highlight the version of the SQL Server is 12.0.5000 (SQL Server 2014 SP2)
  1:

Next, I delete the contents of the Log and Update Cache. See screenshot below;

Phase 2:

Now, I run a CU (10) patch for SP2: https://www.microsoft.com/en-us/download/confirmation.aspx?id=53592 This should bring the version up to 12.00.5571 if successful.

The only thing i see here during the setup is that it does not pick up the current version for the instance. NOTE: I have seen this in multiple scenarios which is not related to this and setup did complete successfully. Now I am more interested to see if this completes successfully :P

Phase 3

Setup did complete successfully as I was expecting and SQL has been patched. Now lets do some few checks to verify the behavior.See screenshot for successful intall and noticed highlighted at the bottom the location of the new summary txt file for the patch.

First lets connect to SQL Server and observe the version. Per screenshot, Yes the SQL Server is now at 12.0.5571 as mentioned earlier in the test.

Now lets finally check what happens in the Log and Update Cache folders.

We notice that new folders and files are created for the patch that we run successfully. This is the expected behavior of a SQL setup.

It is valid to say that yes you can delete the contents of Log and Update Cache folders in Setup Bootstrap folder and be able to still perform future patching/upgrade on the SQL Server.

HTH someone :)


SQLDriverConnect fails when attempting to connect to Failover_Partner

When database mirroring fails over to the mirror machine, the server runs startup code on the database including recovery steps to ensure the consistency of transactions in the database.  This process can take some time; perhaps on the order of 10 to 20 seconds.

You need to wait for the database on dby to come online.  I would recommend retrying the connection several times, with a wait of a second or two in between.

Don't disable connection pooling, that will only cause you performance problems, and has nothing to do with database mirroring.


Homebrew installed PostgreSQL could not connect, database files are incompatible with server

If you don't have any data, then it seems like a problem with Brew. Did you run brew upgrade, there are two answers on StackOverflow that address this, I've merged both of them together to try the best of both worlds.

Backing up

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;

Making sure you have the latest version

brew update
brew upgrade postgresql

Either, not both reinitialize or try to upgrade your data-dir

Nuke it (start from scratch, leaving the old back up)

initdb /usr/local/var/postgres -E utf8

Try to upgrade it

pg_upgrade -b /usr/local/Cellar/postgresql/$myOldVersion/bin/ -B /usr/local/Cellar/postgresql/$myNewVersion/bin -d /usr/local/var/.postgres_bak -D /usr/local/var/postgres

Restart server.

brew services start postgresql

Delete the backup, if the server restarts and everything works remove the backup

sudo rm -rf /usr/local/var/.postgres_bak


Is there a way to list all tables and when they were last accessed?

Jon gives you some links to SQL Server audit information in the comments. That is a definite way to see who is doing what and when on a server. Depending on what you are looking for that may be a good enough answer. Auditing isn't free, though. Capturing some of the data required can be expensive to trak and take some time to track.

One approach here - depending on what you are looking to do with this information is to look at the DMV - sys.dm.db_index_usage_stats this DMV shows you various statistics about access (insert, update, delete, scan, seek and lookups) for your indexes. You can join this DMV to tables that contain information about your tables or views like sys.tables or sys.indexes to see the details.

There are some caveats about this approach (data gets reset when SQL is restarted, there is a bug in SQL 2012 where the stats are apparently reset after an index rebuild, and other caveats described by Joe Scan in a couple posts) You can see an example of querying this data in this post from 2007 written for SQL 2005 but still most applies today.


used:
WITH sub AS (
    SELECT
        t1.itemcode as sapitemcode,
        t1.CodeBars as Barcode,
        t1.ItemName as description,
        LEFT(t1.ItemName,20) as short_description,
        --MAX(case when T0.PriceList = 1 then t0.Price else null end) as price_1,
        case when T0.PriceList = 1 then t0.Price else null end as price_1,
        NULL as precio_tableta,
        CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
        --CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
        case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
        (   SELECT T5.[ItemCode]
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) AS col1,
        (   SELECT T6.[U_NAME] as 'User that created Item'
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) aS col2,
        (   SELECT t5.updatedate
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) AS col3,
        CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto,
        t1.U_GRUPOA as Grupo,
        t1.U_GRUPOB as Departamento,
        t1.U_GRUPOC as Categoria,
        t1.NumInSale as ItemsPerSalesUnit,
        t1.SellItem as SellItem
    from ITM1 T0
    inner join oitm t1 on t0.itemcode = t1.itemcode
    inner join ouom t2 on t2.uomentry = t0.uomentry
    where t1.ItemCode='CID-11418386891'
    and PriceList='1'
)
SELECT sub.sapitemcode,
    sub.Barcode,
    sub.description,
    sub.short_description,
    MAX(sub.price_1),
    sub.precio_tableta,
    sub.Price_level_1,
    sub.uom_group_id,
    sub.col1,
    sub.col2,
    sub.col3,
    sub.Impuesto,
    sub.Grupo,
    sub.Departamento,
    sub.Categoria,
    sub.ItemsPerSalesUnit,
    sub.SellItem
FROM sub
GROUP BY sub.Barcode,
    sub.description,
    sub.short_description,
    sub.precio_tableta,
    sub.Price_level_1,
    sub.uom_group_id,
    sub.col1,
    sub.col2,
    sub.col3,
    sub.Impuesto,
    sub.Grupo,
    sub.Departamento,
    sub.Categoria,
    sub.ItemsPerSalesUnit,
    sub.SellItem


ERROR: recursive reference to query "najdi_tot" must not appear within its non-recursive term

The problem was caused by multiplication in the recursive calls. When I deleted one of them, the problem was solved:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv
    WHERE sv.pod = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Then I realized that the relation is symmetrical - at the first call I get all the linked rows and recursion only adds the row I had in the beginning. So I removed the recursion at all and everything is OK:

  WITH najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
  )
  SELECT array_agg(sj) FROM najdi_tot


Mysqldump error : Couldn't execute 'show table status

The database size itself is very big and that seems to be the reason of lost connection to mysql server.

You can try the below command which will try to take the backup after increasing the net_write_timeout and max_allowed_packet

mysql -u root -p -e "SET GLOBAL net_write_timeout=300; SET GLOBAL max_allowed_packet=1024*1024*1024;"

This command will increase the timeout and max_allowed_packet size.
After this, run the below command to take the dump

./mysqldump -u root -p mydatabase > /Users/myuser/Documents/mydatabase.sql

If you still get error, please provide exact error you are getting in the mysql error logs.


Display department name,the number of employees and their names

You need to fetch the number of employees in a sub-query and then use this:

select
  q.total as "num of employees",
  d.department_name,
  e.employee_name
from
  departments d join
  employees e on d.dept_id=e.dept_id join
  (
    select
      count(dept_id) as total,
      dept_id
    from
      employees
    group by department_id
    ) q on d.dept_id=q.dept_id
group by d.department_name, e.employee_name


To find the latest status of all message (assuming receiptID is an auto-incrementing key) you should use a window function:

SELECT Status
     , messageID
FROM (SELECT Status
           , messageID
           , Row_Number() over (partition by messageID order by receiptID desc) priority
      FROM Receipt) PrioritizedStatus
WHERE PrioritizedStatus.priority = 1


MySQL database cluster vs. replication setup for large crawler indexes

Crawler:

Put this process on a separate server (or multiple servers).
If practical, use multi-row INSERTs to batch-insert the rows.
See this for more tips on high-speed ingestion:  http://mysql.rjweb.org/doc.php/staging_table
Write to the Master (or, if allowed by the cluster, any of the Masters).

For the web side:

Have multiple servers with Apache / nginx / whatever receiving the requests.
Send the requests to the Slave(s) (or nodes).
Use round-robin (if there is more than one server for reading).

Other things:

300% CPU is high.  Let's see the worst offender, plus SHOW CREATE TABLE.
Your dataset will probably be very large, so shrink datatypes where practical, and normalize where appropriate.  (The above link has more on normalizing.)
So far, a single Master seems to be adequate, hence any of the replication or clustering options is still viable.
All writes are performed on all servers.  This puts a cap on how much you can scale.  The only way to go beyond that is via "sharding".
Read scaling can be achieved by adding more Slaves, virtually without limit.  This applies to all flavors of replication/clustering.
Indexing -- Use FULLTEXT for searching for "words"; use regular INDEXes where appropriate.
Since neither FULLTEXT nor INDEX handles "model numbers" and "synonyms", you may need to sanitize the data as it is inserted.


How to improve SELECT performance on a big ORACLE view?

No, an order by in the view will not help. You may want to do two things. First of all you should ensure that student_number is indexed in the underlying table. Plus if the big view joins multiple tables you should ensure that the join columns are indexed.

Second you may want to use a number like this:

WHERE student_number=123456

instead of

WHERE student_number='123456'

if student_number is numeric to avoid type conversions.


Once you enable DB2 as an Enterprise GEoDB, it should work.
The steps to enable using Arcgis desktop tool

http://resources.arcgis.com/en/help/main/10.1/index.html#//0029000000sn000000


Why can the target of the OUTPUT INTO clause not be a view?

From MSDN - Output clause:

"The OUTPUT clause is not supported in the following statements:

DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
INSERT statements that contain an EXECUTE statement.
Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
"


Is dimensional modelling useful for modelling tables of calculations?

If you could meaningfully slice the data by key1, key2 or key3 then you could make dimensions out of them.  In that case you would have separate dimension tables that had all the values of each key, and any relevant attributes (even just a human-readable description of what the value represents) or groupings (roll-ups of the key values).

Each dimension table would have the key value and its attributes, and would link to the fact table on its key.

If your values (Var X) are additive across rows (i.e. all the values added together for Key1='Foo' mean something) then you can use the keys to slice the data.

I don't think that relationships between the Var columns are really relevant to dimensional modelling unless you want to unpivot them and indentify them by a 'variable type' dimension rather than separate columns

Beyond that, the question is a bit vague to really get a clear view of what you are trying to achieve and why you want to make a dimensional model of your data in the first place.  Maybe if you can clarify your goals a bit we can give you a more meaningful answer.

EDIT:
Dimensional modelling is just identifying the axes by which you want to slice data in aggregate.  Sometimes a dimension might be very simple - just a code.  Sometimes it may be complex with lots of sub-attributes - such as an insurance policy.  A dimension lets you slice data in aggregate, either by its key or by rolling up the data by some attribute of the dimension.

If the VarX columns are additive between rows - i.e it makes sense to aggregate (for example) var2 for two or more rows - then a dimensional structure might make sense for reporting.  One key point of a dimensional model is that it tends to facilitate efficient queries in aggregate, and plays nicely with OLAP tools.

So, if I had values 'Foo', 'Bar', 'Wibble', 'Blarg' for key1 and values 'A', 'B', 'C' for key2 then I might have a few rows that looked a bit like:

Key1      Key2       Var1       [. . .]
--------- ---------- ---------- ----------
Foo       A          10         . . .
Bar       A          20         . . .
Foo       B          50         . . .

If I wanted to see the total of Var1 for all Key2='A' then the reporting tool would allow me to slice on Key2 and add up all the rows.  If I wanted to see the total of Var1 for Key1='Foo' then I could do something similar.  In this instance the total of Var1 for Key2 = 'A' would be 30.

If this is the sort of thing you want to do the for data then a dimensional model could help, and would allow you to put up a statistical reporting facility with an off-the-shelf reporting tool such as Report Builder.

A dimension table for Key1 might look like

Key1    Key1Desc                 Key1Grouping
------- ------------------------ -------------
Foo     Description of 'Foo'     Group A
Bar     Description of 'Bar'     Group A
Wibble  Description of 'Wibble'  Group A
Blarg   Description of 'Blarg'   Group B

By joining this dimension table to the fact table you can then roll up by Key1Grouping and see stats for (say) 'Group A.'  You can calculate ratios of Vars after the base additive measures have been aggregated.

Again - I'm still a bit unsure of quite what you want to know.


oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

According to the query plan, the optimizer expects that the MERGE statement is going to modify 1 row.  It appears to expect the Cartesian join to return a single row as well.  Based on the fact that the query isn't returning in a few milliseconds, that would generally imply that the statistics on one or more of the objects (tables or indexes) are substantially incorrect.  When was the last time statistics were gathered on these tables and their dependent indexes?  Has the actual data in the tables changed substantially since that time?


Always On Availability Groups as a Secondary Read Only Database

The secondaries can be used as read only. Keep in mind data is still being added to the secondaries in near real time.
Transaction log backups are conducted against the primary AG instance.
If the secondary can not keep up with the volume of transactions coming across, it can cause the transaction logs on the primary to fill and continue to grow.
In the event the primary becomes unavailable, traffic would then transfer to the secondary. This is the point of Availability Groups.

If you just want a copy of the database to run reports against, you might look into setting up log shipping to another instance. It's a cheaper, and simpler solution than AGs.


Where all can the /*+ PARALLEL */ hint be used?

The Data Warehousing Guide has a chapter on parallel operations that goes over what operations can be parallelized.

You can't parallel-ize a simple CREATE TABLE statement (nor would you want to-- there wouldn't be enough work to be able to parcel it out to multiple worker threads).  You can, however, parallel-ize a CREATE TABLE AS SELECT statement either by specifying the PARALLEL hint in the SELECT or by using the PARALLEL keyword in the CREATE.


Dropping a table will free up the space within the database, but will not release the space back to Windows.  That requires shrinking the database file.  However we don't want the database file full.  We want lots of free space so that as we load more data we don't have to grow the data file frequently.  This causes fragmentation of the data file on the physical disks.

As for making shrinking faster, no there's no way to make it faster.  Shrinking the database requires reading and rewriting most of the data within the database so that all the white space can be released from the database file back to the OS.  All this IO takes time, and causes a lot of fragmentation problems.


How to keep an unique counter per row with PostgreSQL?

SEQUENCE is guaranteed to be unique, and your use-case looks applicable if your number of documents isn't too high (else you have a lot of sequences to manage). Use the RETURNING clause to get the value that was generated by the sequence.
For example, using 'A36' as a document_id:

Per document, you could create a sequence to track the increment.
Managing the sequences will need to be handled with some care. You could perhaps keep a separate table containing the document names and the sequence associated with that document_id to reference when inserting/updating the document_revisions table.

 CREATE SEQUENCE d_r_document_a36_seq;

 INSERT INTO document_revisions (document_id, rev)
 VALUES ('A36',nextval('d_r_document_a36_seq')) RETURNING rev;


mysql threads running high issue for Percona Mysql 8.0.16

Lots of threads?  Do SHOW PROCESSLIST.  If most say Sleep, then there is nothing to worry about.

Load Average = 2 and CPU = 15%.  Do you have 16 cores in your CPU?  Of so, that says you are using about 2 of the 16.  Again, no problem.

Are queries taking a long time?  Let's see them, their EXPLAIN, and SHOW CREATE TABLE.

How much RAM do you have?  How big is your dataset?  What is the value of innodb_buffer_pool_size?


Why does a UNIQUE constraint allow only one NULL?

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL.

It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like WITH CONSIDER_NULLS_EQUAL or WITH ALLOW_ONLY_ONE_NULL.

Of course, hindsight is 20/20.

And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.

You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

This allows more than one NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple NULLs were allowed (especially when it isn't the only column in the index, it has INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:

How filtered indexes could be a more powerful feature


Postgresql: Move database to new tablespace

ALTER DATABASE name SET TABLESPACE new_tablespace

Excerpt from the docs:

  ... changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.


Short answer to what I think you are asking... Yes, you can have multiple binlog_do_db statements.

Long answer...

The binlog and replicate commands can be a bit confusing.  Roughly this is what happens:

If the Master has one or more binlog_do_... statements, they limit what is written to the binlog.  See Bug below.
If the Master has one or more binlog_ignore_... statements, they list what is not written to the binlog; everything else is.
If you have a mixture of the two, I don't know what happens.

For Replication, the binlog is copied to the Slave.  (The binlog could be just sitting there for a form of backup -- No slave involved.  In particular, a Slave can have its own binlog.)

On a Slave, replicate_do_... allows only those things in; replicate_ignore_ tosses the listed things.  A mixture is a mystery.

What you listed (binlog_do_db=mydb3 and replicate-do-db=mydb3) is redundant in that the replicate_do does nothing that the binlog_do didn't already do.  (Namely replicate only mydb3.)  It is more efficient to filter on the Master than on the Slave.  (There are multiple use cases where this is not practical.)  That is, you should have several binlog_do on the master, and nothing on the Slave.

A server that is not being used as a Slave has no use for replicate_*.  (So you should not add it to the Master.)

I am trying to phrase everything carefully because of various topologies that you might not be interested in.  For example...

A "dual-Master" topology has two Masters replicating from each other.  That is, each is both the Master to the other, and the Slave to the other.  I that case each is both a Master and a Slave.  But I digress.

Bug

If the USE command in effect matches any of the binlog_do*, the command will be written.  Note:  USE x; INSERT INTO y.z ... depends on x, not y!  This "bug" (aka 'feature') has tripped up many a DBA.

Side note:

For security, do not include the master-password in my.cnf; do it only when you execute CHANGE MASTER to establish the Master-Slave connection.  (Future versions may disallow it anyway.)


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

The database is corrupt.  Setting it to offline sealed your fate.

If it's mission-critical data that you need to recover, contact Microsoft Support.  It's going to cost $500, but it's worth a shot if you need that data.

Otherwise, restore from backup.


Manual Failover with AlwaysOn availability group

It looks like the answer is Server 2012 R2. 2012 R2 only includes the file share when there are an even number of nodes. So if DR goes offline then the file share will be included in the quorum until such time that DR comes back online.

It also looks like from testing that if you gracefully shutdown connections on secondary's then the primary stays up. So to run from DR for a planned outage means we could failover gracefully and then shut down the servers at the primary site. Because DR became the primary, it will remain up as the last node standing until such time that quorum is restored (and synchronization is complete)


@Michaelsqlbot's comment makes it easy to see the issue

Replication must be going

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M3 -> M2-> M1 -->--+

or

+----<------------<-----+
|                       ^
|                       |
V                       |
+--> M1 -> M3-> M2 -->--+

and M2 does not have log-slave-updates

Given that topology, here is the sequence of events that must have taken place

You created table T1 on M1
M3 has table T1
M2 does not have table T1 because M2 does not have log-slave-updates
You inserted 2 into table T1

If the INSERT was done on M1, M3 has data, M2 has no data and no table T1
If the INSERT was done on M3, M1 has no data, M2 has no data and no table T1

EPILOGUE

Configuring log-slave-updates tells a Slave to take a binlog event from its relay logs, execute it, and record the event in its local binlogs (if log-bin is enabled on the Slave). Thus, M2 not having log-slave-updates enabled in your MultiMaster setup is the root cause.

Big thanks to @Michaelsqlbot for the hint.


What you describe sounds much like horizontal sharding of sorts. That, of course, can be done. To answer your second question, you determine the performance (time/space) impact of your design by building a prototype system and measuring what's important to you.

However, security by obscurity, which seems to be your ultimate goal, is rarely worth the effort.

There are better ways to protect sensitive data, which include encryption (e.g. storage level or transparent database encryption, which obviously should include encrypting backups); row- and column-level access control at the database level; thorough security policies; audit, including possibly wire-level analysis of database traffic; network hardening and intrusion detection; etc. All these methods work well only in combination; looking only at one aspect of your security landscape is counterproductive.


Why is it taking longer to create an index after column size increases?

Remus has helpfully pointed out that the max length of the VARCHAR column impacts the estimated row size and therefore memory grants that SQL Server provides.

I tried to do a bit more research to expand on the "from this on things cascade" part of his answer. I don't have a complete or concise explanation, but here is what I found.

Repro script

I created a full script that generates a fake data set on which index creation takes roughly 10x as long on my machine for the VARCHAR(256) version. The data used is exactly the same, but the first table uses the actual max lengths of 18, 75, 9, 15, 123, and 5, while all columns use a max length of 256 in the second table.

Keying the original table

Here we see that the original query completes in about 20 seconds and the logical reads are equal to the table size of ~1.5GB (195K pages, 8K per page).

-- CPU time = 37674 ms,  elapsed time = 19206 ms.
-- Table 'testVarchar'. Scan count 9, logical reads 194490, physical reads 0
CREATE CLUSTERED INDEX IX_testVarchar
ON dbo.testVarchar (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

Keying the VARCHAR(256) table

For the VARCHAR(256) table, we see that the elapsed time has increased dramatically.

Interestingly, neither the CPU time nor the logical reads increase. This makes sense given that the table has the exact same data, but it doesn't explain why the elapsed time is so much slower.

-- CPU time = 33212 ms,  elapsed time = 263134 ms.
-- Table 'testVarchar256'. Scan count 9, logical reads 194491
CREATE CLUSTERED INDEX IX_testVarchar256
ON dbo.testVarchar256 (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

I/O and wait stats: original

If we capture a bit more detail (using p_perfMon, a procedure that I wrote), we can see that the vast majority of the I/O is performed on the LOG file. We see a relatively modest amount of I/O on the actual ROWS (the main data file), and the primary wait type is LATCH_EX, indicating in-memory page contention.

We can also see that my spinning disk is somewhere between "bad" and "shockingly bad", according to Paul Randal :)

I/O and wait stats: VARCHAR(256)

For the VARCHAR(256) version, the I/O and wait stats look completely different! Here we see a huge increase in the I/O on the data file (ROWS), and the stall times now make Paul Randal simply say "WOW!".

It's not surprising that the #1 wait type is now IO_COMPLETION. But why is so much I/O generated?

Actual query plan: VARCHAR(256)

From the query plan, we can see that the Sort operator has a recursive spill (5 levels deep!) in the VARCHAR(256) version of the query. (There is no spill at all in the original version.)

Live query progress: VARCHAR(256)

We can use sys.dm_exec_query_profiles to view live query progress in SQL 2014+. In the original version, the entire Table Scan and Sort are processed without any spills (spill_page_count remains 0 throughout).

In the VARCHAR(256) version, however, we can see that page spills quickly accumulate for the Sort operator. Here is a snapshot of the query progress just before the query completes. The data here is aggregated across all threads.

If I dig into each thread individually, I see that 2 threads complete the sort within about 5 seconds (@20 seconds overall, after 15 seconds spent on the table scan). If all threads progressed at this rate, the VARCHAR(256) index creation would have completed in roughly the same time as the original table.

However, the remaining 6 threads progress at a much slower rate. This may be due to the way that memory is allocated and the way that the threads are being held up by I/O as they are spilling data. I don't know for sure though.

What can you do?

There are a number of things you might considering trying:

Work with the vendor to roll back to a previous version. If that's not possible, let the vendor that you are not happy with this change so that they can consider reverting it in a future release.
When adding your index, consider using OPTION (MAXDOP X) where X is a lower number than your current server-level setting. When I used OPTION (MAXDOP 2) on this specific data set on my machine, the VARCHAR(256) version completed in 25 seconds (compared to 3-4 minutes with 8 threads!). It's possible that the spilling behavior is exacerbated by higher parallelism.
If additional hardware investment is a possibility, profile the I/O (the likely bottleneck) on your system and consider using an SSD to reduce the latency of the I/O incurred by spills.

Further reading

Paul White has a nice blog post on the internals of SQL Server sorts that may be of interest. It does talk a little bit about spilling, thread skew, and memory allocation for parallel sorts.


No, pgpool is an all or nothing solution.  All you can control is the precentage of read queries going to each host, not what kinds of (read) queries.


SELECTing data based on a specific time frame

In order to answer this, I did the following:

Created the table:

CREATE TABLE cust (cust_code VARCHAR(20), sugg_order integer, ordered_qty integer, ord_date date);

Created some sample data (note how it spans from early 2017 - before Apr 1st) to after today.

INSERT INTO cust VALUES ('cust_1', 45, 450, '2018-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 350, '2018-02-28');
INSERT INTO cust VALUES ('cust_1', 45,  50, '2018-03-28');
INSERT INTO cust VALUES ('cust_1', 45, 451, '2018-04-01');
INSERT INTO cust VALUES ('cust_1', 45, 150, '2018-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  87, '2018-05-28');
INSERT INTO cust VALUES ('cust_1', 45,  93, '2018-06-28');
INSERT INTO cust VALUES ('cust_1', 45,  56, '2018-07-28');
INSERT INTO cust VALUES ('cust_1', 45, 400, '2018-08-28');
INSERT INTO cust VALUES ('cust_1', 45, 234, '2017-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 432, '2017-02-28');
INSERT INTO cust VALUES ('cust_1', 45, 657, '2017-03-28');
INSERT INTO cust VALUES ('cust_1', 45,  43, '2017-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  32, '2017-05-28');
INSERT INTO cust VALUES ('cust_1', 45, 111, '2017-06-28');

WITH cte1 AS
(
  SELECT
    CASE
      WHEN DATE_PART('MONTH', NOW()) >= 4
        THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT,     '9999')     || '04' || '01', 'YYYYMMDD')
        ELSE TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT - 1, '9999')  || '04' || '01', 'YYYYMMDD')
    END AS start_tax_year
)
SELECT * FROM cust, cte1
WHERE ord_date <= NOW() AND ord_date >= cte1.start_tax_year
ORDER BY ord_date

(There's a dbfiddle here)

I then ran 2 queries varying this line:

WHEN DATE_PART('MONTH', NOW()) >= 4 -- also used < 4
                                   -- to simulate a previous year.

Results with >= 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45     450  2018-04-01     2018-04-01
   cust_1         45     150  2018-04-28     2018-04-01

Only two records - which is to be expected since we're only at the beginning of the tax year - just into May. A check shows that there are, in fact, only two records between today (May 10th) and Apr 1st.

Results with < 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45      43  2017-04-28     2017-04-01
   cust_1         45      32  2017-05-28     2017-04-01
   cust_1         45     111  2017-06-28     2017-04-01
   cust_1         45     450  2018-01-28     2017-04-01
   cust_1         45     350  2018-02-28     2017-04-01
   cust_1         45      50  2018-03-28     2017-04-01
   cust_1         45     451  2018-04-01     2017-04-01
   cust_1         45     150  2018-04-28     2017-04-01

8 records - again to be expected, as the period between now and Apr 1st 2017 covers most of the data. If the date as sometime late in 2017, it would pick up the records between then and Apr 1st 2017.

The query is rather convoluted:

THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT, '9999')     || '04' || '01', 'YYYYMMDD')

The whole point of the CTE is to get April 1st of the correct tax year - i.e. the one we're in at the moment, even if we've gone into the next calendar year.

1) get DATE_PART from NOW() cast as an INT when we're beyond April first in the year where the query is being performed. This becomes

DATE_PART('YEAR', NOW())::INT - 1, '9999')

if the month part of the current year is < 4, it's before April, so you have to go back to April 1st of the previous year - which is where the -1 comes in.

2) once you've got the correct year for April 1st, then all the rest is the same. You then use TO_CHAR to transform this into a string and then using the double pipe characters (||) to concatenate the month (04) and day (01) to the correct year. Then use TO_DATE to convert the correct string (20180401) using the format string 'YYYYMMDD'.

3) then use the CTE to feed this value into the main query - obtain the desired records. Et voilà.


SqlServer Database replication in remote datacenter. Which kind of replication?

Ok, if you need to replicate data from Europe (SQL Server 2016 Web Edition) to America, lets see what options we have:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2016
Below features do NOT fit for you:

Database Mirroring (in Web Edition SQL Server can be only in Witness role, it can not be a primary or secondary)
Availability Groups / Failover Cluster Instances (mostly Enterprise / Standard Edition level features)
Transactional / Snapshot Replication  (in Web Edition SQL Server can be only in Subscriber role, can't be a Publisher or Distributor)

You are left only with Log Shipping option, it works in Web Edition
Log Shipping has configurable delay between log backups on primary and log restores on secondary


Looping through tables where the name increments monthly

Here is a quick solution

DECLARE @command VARCHAR(8000)
DECLARE @Date DATE = '2012-01-01'
DECLARE @Year SMALLINT
DECLARE @Month SMALLINT

WHILE @Date <= '2013-06-30'
BEGIN
    SET @Year = Year(@Date)
    SET @Month = month(@Date)

    SET @command = '
  UPDATE customers
  SET Account_Number =
        (SELECT Account_Number
         FROM Hist' + right('00' + convert(VARCHAR(2), @Month), 2) + '_'
                    + right('00' + convert(VARCHAR(4), @Year), 2) + '
         WHERE Account_Number = customers.Account_Number
        )
  WHERE Account_Number = 0
    AND Date >= ''' + convert(varchar(10),@Date,120) +
    ''' and < ''' + convert(varchar(10),dateadd(month, 1, @Date),120) +
  ''';'

    PRINT @command

    SET @date = dateadd(month, 1, @Date)
END


How do you monitor slow query in AWS RDS Postgres

I would reconfigure parameter group settings to create only one logfile per day which is much easier to download and analyze than many smaller files. And configure slow query logging based on your requirements.

Since You're AWS RDS user you can enable performance insights which might be helpful in Your case.


ORA-03113 when starting up an Oracle 11g database

From
ParnassusData:

  This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

  ARGUMENTS:

  Arg [a] - Maximum Undo record number in Undo block

  Arg [b]  -  Undo record number from Redo block

  Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

  Oracle is unable to roll back an uncommitted transaction in its transaction table. You probably have corruption in either the rollback/undo segment or corruption in the object in which the rollback/undo segment is trying to apply the undo record.

  Refer to Note: 39283.1 for more details on the description of this error.

Oracle Note  39283.1 says more or less the same.

Here is a link where somebody solved the problem by creating a new  undo tablespace: SAP, Databases and other IT stuff

Here are the statements:

-- Creation of a new Undo tablespace:
CREATE UNDO TABLESPACE undotbs
DATAFILE 'c:\oracle\10201\dev\undotbs1.dbf'
SIZE 300M AUTOEXTEND ON NEXT 20M
MAXSIZE 800M;

-- Change the default undo tablespace to the new one
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs SCOPE=BOTH;

-- Drop the problematic undo tablespace
DROP TABLESPACE oldundotbs;

To create a tablespace  the database must be open.

I don't know how to reproduce this error so I cannot test how to repair the system. I also think I won't get any feedback from the OP. But nevertheless let's think about, how we can proceed.

The best is to open a service request at Oracle Support.
One possibility is to restore the undo tablespace. But maybe the error will occur again.
Then a point in time restore of the whole database will be an option. But this means loss of data.
We will try to repair the system by creating a new undo tablespace an dropping the old one as described above.

To repair the system we unset the UNDO_TABLESPACE parameter. Then the SYSTEM tablespace becomes the default tablespace and I assume the database can be opened now.

startup nomount
alter system reset undo_tablespace scope=spfile;
shutdown
startup

Now we can proceed as above and create and set a new undo tablespace an drop the problem tablespace as described above.


How would I select distinct records from the same table on two different databases?

SELECT DISTINCT col1, col2, ...
FROM
(
  SELECT col1, col2, ... FROM Media_One.dbo.Video
  UNION ALL
  SELECT col1, col2, ... FROM Media_Two.dbo.Video
) AS x;


Inconsistent presence of view in Postgres system tables

The views in information_schema are subject to permission checks.

Specifically, the definition information_schema.tables has this filtering clause at the end (use \d+ information_schema.tables in psql to see this):

  [... long definition cut...]
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))
  AND NOT pg_is_other_temp_schema(nc.oid)
  AND (pg_has_role(c.relowner, 'USAGE'::text)
   OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
    OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

information_schema.views has similar restrictions.

If the user issuing the following query

 select * from information_schema.tables WHERE table_name = 'foo_view'

has no privilege whatsoever on foo_view, it's to be expected that this query returns 0 row.

If a user sees different results on this query between databases PROD and TEST  , it's presumably because that user has different permissions on this object between databases.

As for the client program, DBeaver, it can show all objects in both databases because presumably it skips these permission checks by addressing directly pg_class, pg_type, pg_namespace and so on (the postgres-specific underlying tables implementing the catalog), like for instance psql, rather than the views in information_schema.

Also when you're connected as a superuser, you should see everything in all cases.


So it is the clustered index affected. If it was me I'd probably try DBCC PAGE ('myDB', 1, 952663, 2); on both the restored backup and the broken DB first to see and compare in Winmerge to see if I could infer anything from that (e.g. if both very similar but just some corruption in the page header then you could use DBCC with style 3 on the restored DB to get a good idea of specific rows to restore after REPAIR_ALLOW_DATA_LOSS. Otherwise you'll just need to do a data compare between the two tables and try and see if you can figure out rows that need restoring that way. – Martin Smith


Can I pass data between stages in a maintenance task

Using an SSIS expression on the Notify Operator task properties for the message subject or body, you can construct whatever you want from variables written earlier.


Passing array parameters to a stored procedure

The best ever articles on this matter are by Erland Sommarskog:

Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters
Arrays and Lists in SQL Server 2005 and Beyond- When TVPs Do Not Cut it
Arrays and Lists in SQL Server 2000 and Earlier

He covers all options and explains pretty well.

Sorry for the shortness of the answer, but Erland's article on Arrays is like Joe Celko's books on trees and other SQL treats :)


ORA-02448 during DBMS_REDEFINITION.finish_redef_table ERROR at line 1:
  ORA-02448: constraint does not exist

CAUSE

  Bug 18258770 : ORA-2448 ON DBMS_REDEFINITION.FINISH_REDEF_TABLE

Here is the document link(MOS account required)
ORA-02448 During DBMS_REDEFINITION.finish_redef_table (Doc ID 2021843.1)


Can a maintenance workload after a SQL Server reboot cause SQL outage or issue when application load resumes?

If the maintenance tasks were complete, then the resources would be free. If not, then you'll have blocking (unless you have online index rebuilds etc)

My random guess is some client command timeout, which issues an ABORT, which left locks and transactions. Your node failover is the same as closing all connections. The timeout could have been caused by cold cache, no cached query plans etc

Saying that, I have seen brain dead polling process overload a database by opening and closing connection 1000s of times per second. And similar. Or one client needs rebooted , normally indicated by ASYNC_NETWORK_IO waits.

Can you reproduce it though? Which will be tricky on a live system

See SQL Server Transaction Timeout for more (both answers)


How to get the basetype of a domain in pg_type?

You can use format_type() to get the correct base data type.

Something along the lines of:

SELECT t.typname as domain_name,
       pg_catalog.format_type(t.typbasetype, t.typtypmod) as data_type,
       pg_catalog.pg_get_constraintdef(c.oid, true) as constraint_definition
FROM pg_catalog.pg_type t
  LEFT JOIN pg_catalog.pg_constraint c ON t.oid = c.contypid
WHERE t.typtype = 'd'
  and t.typname = 'your_domain_name'


Understanding performance of highly unique WHERE clauses

What's probably going on here is that at some point, you ran that first query with a value that had a lot more than 500 rows - so many that it figured it was better to scan 10M rows than do hundreds of thousands of lookups to get the Name and INT109 columns. And then that plan got cached, and reused when you provided a different value which would've benefited from a different plan. SQL made an assumption that it would be better to avoid recompiling than to evaluate each different value you could provide.

When you wrote a different query, it evaluated it fresh, and gave the best plan, although that might not be ideal for a different value.

The best way to fix this is to have an index on (TYPEID, INT109) INCLUDE (ID, Name), so that lookups are not needed, and the plan will seek regardless of the stats around TYPEID. Plus, this index is useful even if you leave out INT109.


Anonymous and Public User Table Design in twitter like application

At first glance of your initial schema design, I liked the normalization of it, but I understand the problem you've run into. So my recommendation to solve that problem was to denormalize a little which essentially you did in your second solution. One table for all types of Users, self-referencing to link an Anonymous User to Public User. I don't know if it's necessarily considered an anti-pattern but it's definitely a common design solution in relational databases to use self-referencing tables.
My only recommendation on top of that is to add a dedicated field, such as a boolean, whose purpose is to define if the record is Anonymous vs Public (e.g. isPublic). That way, you can even have an Anonymous User who doesn't necessarily link to a Public User and that it's explicit it's an Anonymous User record (when the public field is NULL).


Filtered Index hint rejected by SQL Server

I can reproduce this in a database with PARAMETERIZATION FORCED.

CREATE DATABASE D1

ALTER DATABASE D1 SET PARAMETERIZATION FORCED

GO

USE D1

CREATE TABLE tbl_test
(
CommentDateTime DATETIME,
name VARCHAR(50),
comment VARCHAR(50),
CommentByType VARCHAR(10)
);

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT'
--OPTION (RECOMPILE)
;

The literal then gets parameterised and it is no longer guaranteed that the filtered index will match

where CommentByType = @0

In which case OPTION (RECOMPILE) allows the hint to succeed.


SQL Server 2016 login language display is wrong

If the change (Alter login LoginName  with DEFAULT_LANGUAGE  = English) applied successfully (Commands completed successfully.) the language must be changed though server default is Arabic.

However, you have to disconnect the session and connect with new session then try select @@language.

If you have already done so, and the problem still persist, check SQL Error log if there is any issue reported while changing language for particular login.


How to deal with a person that may have multiple alias names?

Option 2 is the right way for graph databases, especially if :Alias nodes all point to the :Person node (or whatever label you use to differentiate a person entry from an alias) who has that alias. The :Person node itself can also have an :Alias label, allowing their name property to also be considered when performing queries.

For example, if you had a graph like this:

(:Alias {name:'Joe Shmoe'})-[:ALIAS_FOR]->(joe:Person:Alias {name:'Joe Jackson'})
(:Alias {name:'Joe Anybody'})-[:ALIAS_FOR]->(joe)
(:Alias {name:'Joseph Jones'})-[:ALIAS_FOR]->(joe)

Then you could issue a query with an $alias parameter like:

MATCH (:Alias {name:$alias})-[:ALIAS_FOR*0..1]->(person:Person)
RETURN person

You should have an index on :Alias(name) for quick lookup, this allows lookup of the person for their name or any alias associated with them (remember their :Person node is also an :Alias node, so this will match too).

And if you need to do case insensitive searches, or searches across multiple fields, then you may want to look at leveraging fulltext schema indexes introduced in Neo4j 3.5.x


Enforcing type correctness for foreign keys into a generic typed table

Your approach is the one I would use.

You forgot a unique constraint:

ALTER TABLE equipment ADD UNIQUE(id, type);

That will be the target of the foreign key constraints.

I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage space.


Yes, you can use SQL Server Management Studio, even if it's an Express version to connect to any SQL Server,you have TCP/IP connectivity to. Just launch it, enter the DNS host name or IP address in the 'Server Name' box and hit Connect.

Two things may prevent this from working:

Your SQL Server isn't set up for TCP/IP connectivity. This is the default setting from version 2005 onwards, and can be changed using the SQL Server Configuration Manager.

There is a firewall between you and your SQL Server blocking TCP/IP traffic. This is an entirely sensible construction: you do NOT want your database server to be available from the general Internet, as this is a huge security risk. In fact, if your hosting company allows this kind of access by default, I'd be looking for a different provider...

Anyway, what seems to be needed in your scenario is: a) the hosting company enabling TCP/IP on your SQL Server instance, b) them providing you with secure access to the IP address that instance is running on. This will involve some kind of VPN or SSH port-forwarding soluition, or at least an IP filter, where only 'trusted' IP addresses can access the SQL Server (which is not a great solution, but can be used if nothing else can be implemented). You'll have to discuss these requirements with your hosting company.


SQL Error [42501]: ERROR: permission denied for table

The ALTER DEFAULT PRIVILEGES statement you ran will only affect tables created by postgres. If a different user creator creates the tables, you need
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;


How to rollback the identity seed after deadlock

I believe your best bet is going to be to use a TRY-CATCH.  I've done two things here.  First declare and get your "oldID" before your transaction begins.  This will help to keep your transaction time down and avoid deadlocks as much as possible.  Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again.  This way regardless of the error you will re-set the seed.  I believe this will work even in a deadlock situation.

DECLARE @oldID bigint
SELECT @oldID = MAX(id) from identtest where id<3000

BEGIN TRANSACTION

BEGIN TRY
    SET IDENTITY_INSERT identtest ON;
    INSERT INTO identtest (id, x) VALUES (4000,'y');
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
    SET IDENTITY_INSERT identtest OFF;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    DBCC CHECKIDENT ('identtest', RESEED, @oldID) WITH NO_INFOMSGS;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

DBCC CHECKIDENT ('identtest', NORESEED)

All of that being said you may be better off switching your logic around a bit.  Would it be possible for you to set your "normal" inserts to start at say 20000000 and then your IDENTITY_INSERTS to start at 10000000?  This way regardless of your insert the seed will stay at the larger "normal" value.  It sounds like you are a bit far in to do this, but you never know.


How do i set up an Alert that tells me when a replication of data failed?

You can set up SQL Server Agent Alerts to trap specific error numbers and respond to them by sending email notifications, or running jobs. In Management Studio, drill down to SQL Server Agent, Alerts. You might have a few defaults in there. If you don't have "Replication: agent success" and "Replication: agent failure", you can create them to watch for error numbers 14150 and 14151, respectively.

HOWEVER, since the agent success message isn't written to the SQL Server error log, you won't be able to have alerts on it by default. I just went through this trying to get email alerts for permission denied errors. You can use sp_altermessage to enable logging for a specific error message, but be careful that it isn't a frequent message, or you'll have some serious error log bloat on your hands. You don't need to do this to enable the agent failure message, which is logged by default.

EXEC sp_altermessage 14150, 'WITH_LOG', 'true'

Then in the "Response" page for the alert, you can choose which operators to notify. Set them up under SQL Server Agent, Operators if you don't have any defined.


Make a backup of the:

existing datafiles (SYSTEM01.DBF, SYSAUX01.DBF, UNDOTBS01.DBF, USERS01.DBF or similar) and tempfile (TEMP01.DBF - actually its not really needed, but keep it simple...)
controlfiles (CONTROL01.CTL, CONTROL02.CTL, or similar)
redo logs (REDO01.LOG, REDO02.LOG, etc..)
and the database parameterfile (ORACLE_HOME/database/initXE.ora or    spfileXE.ora)

Note the path of these files, as you should copy them back to their original path if you want to make it simple for yourself.
You can simply move/rename the whole existing Oracle directory if you want.

Install the new operating system, install Oracle 10g XE again at the same directory (so delete the previous installation after making backup of the above), let it create a new database and listener. When its done, stop the database and the belonging service at Windows Services by stopping the OracleServiceXE service. Now copy back the above files at their original place, and start the database by starting the OracleServiceXE service. The database should start and open after performing a crash recovery if your computer was shut down uncleanly earlier.

Note that, this is and oversimplified example of a way more complex process, and it assumes that you put everything to the place where they originally were.


Actually, if you don't use --gzip parameter, data is at bson format what is not compressed and you can look that data with bsondump program.
What mongodump do, is read data from database and that result is of course not compressed.

Here is different between those two from my system

-rw-r--r-- 1 root root 61M Mar 16 06:48 data.bson
-rw-r--r-- 1 root root 5.3M Mar 16 06:48 data.bson.gz

Over 10 fold compression ration.


Cannot drop database with logical replication

Check out the fine documentation:

  If this fails, either because the remote host is not reachable or because the remote replication slot cannot be dropped or does not exist or never existed, the DROP SUBSCRIPTION command will fail. To proceed in this situation, disassociate the subscription from the replication slot by executing ALTER SUBSCRIPTION ... SET (slot_name = NONE).


How to grant MySQL user privilege to "SHOW TABLES"

From the official MySQL Documentation, You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege.

Check the grants for the connected user. To check grants:

SHOW GRANTS FOR root(user);

For example, if you wish grant SELECT privileges to a user, login as root (user with grant privilege) and issue the following command statement.

GRANT SELECT ON *.* to 'user'@'host';

Also, from the same document, you can use the mysqlshow command to display database and table information.

I hope this answer will help you. Thanks.


My preference is to handle this with a stored procedure rather than a trigger, but that's an option if you have a lot of legacy code running unwrapped INSERTs.

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 0);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id) VALUES (TestID);
    DECLARE TestVersionID INT;
    SET TestVersionID = LAST_INSERT_ID();

    UPDATE test SET version_id = TestVersionID WHERE id = TestID;

    COMMIT;
    END//
DELIMITER ;

Apologies if I screwed up the MySQL syntax, I've been working in MS SQL 90% of the time for the past ten years.

Note that this code temporarily sets the value of version_id to zero, to satisfy the FK.  You'd need a dummy record in test_versions with such an ID.  However, the field is updated before the transaction commits, so no queries should ever see this value.

Edit: if you are really concerned about the INSERT performance of this sproc, you could simplify it by giving test_version a composite PK on test_id and version_id, where version_id is a sequence (starting at 1) for each test.  You would need to retrieve only one AUTO_INCREMENT value:

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 1);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id, version_id) VALUES (TestID, 1);

    COMMIT;
    END//
DELIMITER ;

As you can see, version_id can be assumed to be 1 for the first record, so there's no need to UPDATE test.  You also don't need a dummy record #0 in test_version.  However, the broader PK would make indices on test_version slightly bigger.  Using a narrow integer for version_id would help.


How do I get a list of values for column x GROUPed BY column y?

Instead of CONCAT, use LISTAGG:

select
  p.written, upper(p.title) as title, p.pid,
  upper(listagg(a.sname, ', ') within group (order by a.sname)) as authors
from publications p
join author_publications ap on p.pid = ap.pid
join authors a on ap.aid = a.aid
group by p.written, p.title, p.pid
order by p.written;

Result:

WRITTEN TITLE                                                         PID AUTHORS
------- ------------------------------------------------------------ ---- ---------------
   1970 A RELATIONAL MODEL OF DATA FOR LARGE SHARED DATA BANKS          1 CODD
   1976 COMMENTARY ON UNIX                                              7 LIONS
   1990 THE RELATIONAL MODEL FOR DATABASE MANAGEMENT                    2 CODD
   2000 THE THIRD MANIFESTO                                             4 DARWIN, DATE
   2002 TEMPORAL DATA AND THE RELATIONAL MODEL                          5 DARWIN, DATE
   2003 AN INTRODUCTION TO DATABASE SYSTEMS                             3 DATE
   2005 DATABASE IN DEPTH: RELATIONAL THEORY FOR PRACTITIONERS          6 DATE


Configure Connection String to Use Target Machine Credentials

The dtsx package runs where you install and execute it. Usually in SSISDB.
Then you can create a sql server agent job to run it.
The connection string specifies where the action (executing some sql script, or as a data source/destination in a dataflow as examples) will be executed.
You can install the package in every server you want and then refer in the connection string for example to localhost to run it against the same server.
You can also manage parameters and design a parametric connection string as you pointed out.
If you have a list of servers to contact with the connection string, you have to loop into the list and place the values in the connection string and then execute the task you want using parameterized connection string. So you will contact every server you want and execute what you need.
But there will be always only one server hat runs the solution.
If you have a job in every of your servers that point to a local SSIDB with a local dtsx to execute against a local instance you have to manage them with a local environment that define a good connection string for each one of them.


Does ordering by auto-incrementing PK ensure chronological order?

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always".

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1. The manual states this:

  [A]lthough multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

Leaving CACHE value at 1 might hurt performance in an environment with high concurrency, so you will have to choose what is more important to you.

In my view, trying to repurpose SERIAL for tracking order of creation of records is not a good idea, because it potentially creates a maintenance problem: since the sequence's only promise is to provide some unique incrementing value to each nextval request, relying on its behaviour under one circumstance might cause your application to behave unpredictably when the situation changes, e.g. the sequence implementation details are modified in a future Postgres version, or changes in your workload require you to increase the CACHE setting. Using a proper timestamp for orderging is both safer and semantically clearer.


How do I remove backslashes from records in MySQL

DROP TABLE IF EXISTS tmpTable;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable ( BadText VARCHAR(100));

INSERT INTO tmpTable
SELECT 'This is an example of \\\' single, \\\"double, and \\\\ backslash';

select BadText from tmpTable;

update tmpTable
set BadText = replace(replace(replace(BadText,'\\\'','\''),'\\\"','"'),'\\\\','\\');

select BadText as GoodText from tmpTable;


Getting the count of same value in consecutive rows that are ordered by different field

I have a solution that does not need GROUP_CONCAT

PROPOSED SOLUTION

SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
    name,
    (@nametag:=MD5(CONCAT(name,':',result))),
    (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    (@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;

PROPOSED SOLUTION EXECUTED

mysql> SELECT name,consecutive FROM
    -> (SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id) A
    -> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>

SUBQUERY'S OUTPUT

mysql> SELECT
    ->     name,
    ->     (@nametag:=MD5(CONCAT(name,':',result))),
    ->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
    ->     (@name:=@nametag) inc
    -> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

I just answered a similar question 2 days ago (Logic Value of Assignment Expression)


You can change the properties of the article (or of all the articles) when you create the publication.  You can also change this after the fact by editing the publication.  You can setup the replication to drop the tables (the default), truncate the data, delete the data, or do nothing.  IF you need to load the data on the subscriber from scratch you'll need to setup the articles to delete the data.  This means it will take a lot longer to apply the snapshot.  It would be best to make sure that all the foreign keys exist in the publisher so that the publisher knows in what order to drop and recreate the tables on the subscriber.


Query on large database much slower on HDD

There are two problems
PROBLEM #1 : MyISAM
MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data.
See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading
PROBLEM #2 : HDD vs SSD
An HDD must use spindles to move its read/write heads across a disk
An SSD is a random read/write device. It zeros in on blocks to disk faster than HDD.


TDE Performance and Cost Threshold for Parallelism

Whenever you've got an overall general performance question like this, start by asking, "What's the server's primary wait type?"

My favorite way to get that is with sp_BlitzFirst @SinceStartup = 1. (Disclaimer: I'm one of the authors of that open source script.) The first section of that will return your top waits since startup. If you post that output here, you'll get a lot better diagnostic answers.


Select lowest ID and calculate deviation from the lowest

FIRST_VALUE (Transact-SQL)

  Returns the first value in an ordered set of values in SQL Server
  2014.

Partition on mark1 and order by mark2.

Something like this should do it for you:

select T.*,
       100 * (T.mass - T.FirstMass) / T.FirstMass as Deviation
from (
     select T.*,
            first_value(T.mass) over(partition by T.mark1 order by T.mark2) as FirstMass
     from dbo.YourTable as T
     ) as T;

SQL Fiddle


It's doing about 45k requests/sec

Do not do 45k requests per second.

On the insert side you can batch them and use batches of inserts.

Possibly use a stored procedure - that should be a little less intense on the CPU (less parsing).

But mostly consider whether you can cut down the number of operations. BulkCopy is quite nice and you can basically do two operations per second with all inserts and updates in one run (bulkcopy into temp table, merge into target).

This would likey liminate nearly all your CPU.

Yes, this may require some serious reprogramming. But if you need to get down the CPU usage, this is the way to do it.


LOG backup Always ON using Ola Hallengren database backup script does not carry out a backup

Your availability group is set to prefer backups on the secondary, and by default Ola's script is going to only run log backups on the node that is preferred.

Use the @OverrideBackupPreference='Y' parameter in the backup command to override that setting so that it will take the log backup on the node where the script runs regardless of the backup preference setting of the availability group.


How to backup a large (100gb+) microsoft sql database for occasional "offline" use?

If the primary goal is to save money (i.e. not licensing Standard, let alone Enterprise, edition) you’ll need to be creative and accept a certain amount of inconvenience. Some ideas:

Purge any data you know you don't need
Break it up into smaller database that do fit on Express (by date, by table, by subject, whatever fits)
Export it to an open source system (Postrgres, MySQL)
Save it in on the cloud (Azure, others), using a plan that best supports infrequent access
Store it outside of a database, on a per-table basis (ASCII file, Spreadsheet)


For example, when only title changes, do not update content. Or when only content changes, do not update title. Or when they both change, both of them have to be updated.

Why? When either of them changes just update both of them. It's an MVCC database and any change to the table will result in a whole new row being written and the old one being marked as dead.

UPDATE post
SET title = $2, content = $3
WHERE post_id = $1
  AND title <> $2
  OR content <> $3;

And don't use VARCHAR(100) use text


I see you restored the tables. Your issue seems to be the owner of either a view or a stored procedure

You need to replace all occurrences of

`root`@`%`

in the file with

`root`@`localhost`

I would not recommend creating the user root@'%' as that would allow root to have remote access.

Since root@'%' is the owner of the routines, I will create root@'%' like root@localhost

Please run the following:

CREATE TABLE mysql.user_root LIKE mysql.user;
INSERT INTO mysql.user_root
SELECT * FROM mysql.user WHERE user='root' and host='localhost';
UPDATE mysql.user_root SET host='%';
REPLACE INTO mysql.user SELECT * FROM mysql.user_root;
DROP TABLE mysql.user_root;
FLUSH PRIVILEGES;


Move MySQL Database to Amazon EC2 Servers

There is nothing intrinsically wrong with moving a database to AWS EC2.  IN the end, they're just servers, but they're set up in a very specific way.

You have to be fully aware of the limitations of the platform before you start, so those documents that you don't know where to start?  Read them all.

You've got to be aware that there are different ways of working when you start using AWS.  Some fundamental differences between having a server in your server room that you are in complete control of vs a server in AWS' data centre somewhere that somebody else is in control of.

Learn about RDS, learn about different storage types, learn about EC2.  Make sure you understand the costs before you put anything in there.

Use the free year's worth of free instances for all it's worth.  Test, ensure that what you want to do is possible.


Looking for tree organisation for database, based on one to many relationship both ways

A tree is, by definition, one parent with many children, each of which can have many children. What you desire is a generalised network, not a tree.

There are many DBMS products which present a graph (i.e. generalised network) data model. Both items and the relationships between items are first-class objects. Typically both can have arbitrary labels and attributes. These products are also optimised for relationship-based processing such as friends-of-friends, shortest path or cluster detection. If navigating the network is more important for your use case than OLTP point updates or calculating bulk aggregates then one of these DBMS may suit.

From comment:

  Is there a way of having a direction to this network though ? It's for storing school programme data, and the prerequisite knowledge for a certain course for example. (And that course can have many prerequisites and can be prerequisite to others, but there is a direction to the structure.)

All graph DBMSs implemented directed graphs. This means there is a "from" and a "to" in each relationship. This is analogous to, but different from, the "parent" and "child" in a relational database foreign key. A relationship's direction is set when the relationship is written to the DB. Relationships can be read in either direction, however, much like a SQL query can follow a foreign key to find the children given the parent, or the parent given a child.

A hypothetical graph for your school prerequisites might look like:

Basic math   <-- requires --  Advanced math  <--- requires ---¬
        ^                                                     |
        └-------  requires -----------¬                    Medicine
                                      |                       |
Basic chemistry  <-- requires --  Advanced chemistry  <-- requires

Medicine has two prerequisites - advanced math & advanced chemistry. Basic math is a prerequisite for two other subjects.


With multiple files in a file group, when does data start being placed on the 2nd file in the filegroup?

You'll want to preallocate the space on XWing_ExtraData.   At 10MB per autogrow, it'll induce a lot of external file fragmentation and delays whenever SQL needs to expand the file on the disk (performance will be better if you have a large pre-sized file up front).
Then disable autogrowth on XWing so that it can't grow any further otherwise SQL will continue to populate the file until you run out of space, triggering an 1105 error (filegroup full).
With regards to the original file, my preference is to just disable autogrowth via the dialog box (checkbox on the top of the dialog box or via ALTER DATABASE...MODIFY FILE...FILEGROWTH=0. You want to ensure that SQL won't attempt to autogrow the file if the algorithm can choose that file instead of XWing_ExtraData if both need to autogrow to accomodate the next data insertion.


sp_Blitz: Poison wait detected and too much free memory

This is not an over-provisioning issue or even a memory misconfiguration.

RESOURCE_SEMAPHORE: "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."

Depending on which version and build you are using of SQL Server, you may be able to query for large memory grants so that you know where to start on which queries are causing a problem. If you are on SQL 2012 SP3 or greater, SQL 2014 SP2 or greater or SQL 2016 RTM or greater, then you can query for this information. See the query at the bottom of New memory grant query hint MIN_GRANT_PERCENT came to rescue.

Check out Query Plans: Memory Grants and High Row Estimates for more information on memory grants, which is the link inside the link sp_Blitz directed you to.


Internal Stored Procedure Failure - ID_TransUserActionCreate

That's a user-defined error message that someone created on that instance.

DOCS

  Is a user-defined error message number stored in the sys.messages
  catalog view using sp_addmessage. Error numbers for user-defined error
  messages should be greater than 50000. When msg_id is not specified,
  RAISERROR raises an error message with an error number of 50000

You can find it in sys.messages

select *
from sys.messages
where message_id = 50101

I would guess ID_TransUserActionCreate was a column name a table--lbut this is just a guess.


What you are trying to do can be resolved using two operations: an unpivot followed by a pivot.

The table that is most principal to the query you are looking for, Catalog, is document-centric. It stores data in this form:

What | Created by Whom | Edited by Whom

You, however, want a user-centric report. So, first of all, you need to transform the original set to a user-centric one:

Who | Did What | To What

The To What part is not strictly necessary in this case, perhaps, but I leave it for a better illustration of how the data are rearranged in the course of the sought-for transformation. And the transformation itself is called unpivot. Assuming the t-sql tag on your question means SQL Server rather than Sybase, an unpivot in your case can be implemented like this:

SELECT
  u.UserID,
  Action = LEFT(u.Action, LEN(u.Action) - 4)
FROM
  dbo.Catalog AS c
  UNPIVOT
  (
    UserID
    FOR Action IN (CreatedByID, ModifiedByID)
  ) AS u
;

The LEFT() trick simply cuts off the ByID bit from each name, leaving just the verb.

Another way would be like this:

SELECT
  u.UserID,
  u.Action
FROM
  dbo.Catalog AS c
  CROSS APPLY
  (
    VALUES
      (CreatedByID,  'Created'),
      (ModifiedByID, 'Modified')
  ) AS u (UserID, Action)
WHERE
  u.UserID IS NOT NULL
;

I am assuming that a document that has not been modified will have a null in ModifiedByID. Filtering out the Modified entries with a null user ID is what the WHERE clause in the above query for.

Now you could simply group the results by UserID, Action and count the rows in each group. That would give you the output values in this form:

UserID  Action    Count
------  --------  -----
100     Created   10
100     Modified  15
110     Created   8
...     ...       ...

But as you want Created and Modified as separate columns:

UserID  Created  Modified
------  -------  --------
100     10       15
...     ...      ...

you will need to group the results with a pivot. One way to do so is to use the native PIVOT syntax:

SELECT
  UserID,
  Created,
  Modified
FROM
  (
     ... /* the unpivot query (either option) */
  ) AS s
  PIVOT
  (
    COUNT(Action)
    FOR Action IN (Created, Modified)
  ) AS p
;

You could also achieve the same using the older syntax called conditional aggregation:

SELECT
  UserID,
  Created  = COUNT(CASE Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE Action WHEN 'Modified' THEN 1 END)
FROM
  (
     ... /* the unpivot query (either option) */
  ) AS s
GROUP BY
  UserID
;

In the latter case the derived table can be avoided. Instead you would just need to use the FROM clause and, if necessary, the WHERE clause from either unpivoting method. That is, the final conditional aggregation query would look either like this:

SELECT
  u.UserID,
  Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
  dbo.Catalog AS c
  UNPIVOT
  (
    UserID
    FOR Action IN (CreatedByID, ModifiedByID)
  ) AS u
GROUP BY
  u.UserID
;

or like this:

SELECT
  u.UserID,
  Created  = COUNT(CASE u.Action WHEN 'Created'  THEN 1 END),
  Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
  dbo.Catalog AS c
  CROSS APPLY
  (
    VALUES
      (CreatedByID,  'Created'),
      (ModifiedByID, 'Modified')
  ) AS u (UserID, Action)
WHERE
  u.UserID IS NOT NULL
GROUP BY
  u.UserID
;

You can use the final query as a derived table to join the results to the Users table to further connect them with the results from the Log table – in much the same way as you are doing now.


ERROR 1044 (42000): Access denied for user 'globacom'@'localhost' to database 'avi_globacomglobacom_data4.sql'

You need to use forward slashes for paths, even on Windows. The mysql command line is interpreting the sequence \r as a connect command and things blow up. (This quite visible in the error message: database 'avi_globacomglobacom_data4.sql'.)

So try:

source E:/ravi_globacom/globacom_data4.sql

Or don't use source, but shell/cmd redirection instead:

mysql -u globacom -pglobacom < E:\ravi_globacom\globacom_data4.sql

depending on what's most practical.


Addtional configuration when deploying Data Tier Applications?

There is no direct way to set/change the ownership as a part of CREATE or ALTER DATABASE statement.

If you want to enforce that, you can do this by adding a post deployment script which calls sp_changedbowner.

This article describes that in detail. Similar approach can be applied to set the initial file size and growth Rate as well.

Also, as a note: sp_changedbowner is now depreciated, so it is better to use ALTER AUTHORIZATION