texts: mariadb 10.3.13 table_open_cache problems

texts:
Just upgraded mysql 5.6 to mariadb 10.3.13 - now when the server hits open_tables = 2000, my php queries stop working - if I do a flush tables it starts working correctly again. This never happened when I was using mysql - now I can't go a day without having to login and do a flush tables to get things working again

Use WHM / Cpanel to administer my VPS and on the last WHM release it started warning me that the version of MySql (really can't remember what version it was - it was what was loaded when I got my VPS) that I was running was soon coming to an end and I would need to upgrade to SQL 5.7 or MariaDB xxx.  Had been wanting to move to MariaDB for awhile anyway, so that is what I did - WHM recommended the 10.3.13 version.

After some more watching and looking it appears that what makes my open_tables hits the 2000 max was the automatic CPANEL backup routines - which also backup all of my databases at one get go. Doesn't crash anything just causes problems with my PHP application connections - I don't thing the connections get rejected - they just don't return any data .... Turned all of the automatice WHM/CPANEL backups off and things have settled down a little.

table_definition_cache  400
table_open_cache     2000

I still do a mysqldump via cron to do my database backups - only two live databases and they still make the tables_open grow to that 2000 max - just not as fast.

I now run a script that runs every hour to show me some of the variables and here is what I am seeing

after doing a flush tables command both open_tables and open_table_definitions start increasing until open_table_definitions hits 400 it stops increasing while open_tables keeps increasing thru the day.

then when the mysqldumps happen in the early morning hours tables_open hits 2000 (the max setting) and my php queries are not executed

I do not get a PHP error.

I ran the following command so that I could see what was happening on the db side.

SET GLOBAL general_log = 'ON'

Looking at the log, when everything is running OK I see my application connecting, preparing the statement, executing the statement and then disconnecting ....

I did the same thing when it started acting up (i.e. my php application starts to not get a result again)

Looking at the log I see my application connecting, then preparing the statement and then instead of seeing it execute the statement, it prepares the same statement 2 more times and then  disconnects ...

I logged into mysql and did a flush tables command and everything goes back to normal - application connects, prepares a statement, executes it, disconnects ...

But this never happened before I moved to MariaDB - I never messed with the MySQL server  stuff at all - the only time MySQL was restarted was when I did a CENTOS 6 system update and had to reboot the server - would go months without doing a thing on the server ....


texts: How can we automatically replace a table with a view for a specific user?

texts:
We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a mammoth task, and only one user has a problem here. Is there a way to have SELECT queries on a base table automagically SELECT from a view table instead of the base table based on the user making the query? It would be a bonus if I can also base it on the program making the connection. We're using MSSQL 2008.

ie. SELECT * FROM myTable would instead be SELECT * FROM myView if the user executing the query was [user] and the program making the connection was [reporting software name].

EDIT:
Note: The outer reporting software is an old version of Crystal Reports running some massive ugly queries built by the query builder.
Some sample output would be:

Regular Guy:    Other Guy:

 ItemCode        ItemCode
 AAAAAA745       AAAAAA745
 AAABBB672       AAACCC657
 AAACCC657       BBBAAA766
 BBBAAA766       BBBCCC383
 BBBBBB838       AAABBB672
 BBBCCC383       BBBBBB838
 CCCBBB883       CCCBBB883

As we want to order all items with SUBSTRING(ItemCode, 4,3) = 'BBB' at the bottom.


texts: Shrinking T-Log file every weekend basis

texts:
Recently our SQL Server Log drive which has all T-Log files starting growing rapidly.

Considerations

We expanded drive space to 100 GB.
It is AOAG environment, SQL Server 2014.
We have regular backup of T-Log every 15 minutes (but no log drive space is freed up and log drive is increasing more and more each week 50 GB at a lot).
We cannot afford to expand drive each time.
Found the reason to be Rebuild-Index task.
I have deployed Ola Hallengrens code for Index optimization for this.
Re-build task occupies much space and occupies the drive in close to 15 minutes is 50 GB.

I have been thinking of doing this immediately after the index optimization job completes (Ola Hallengrens code for Index Optimization). I am planning to shrink all the log files on a weekly basis.

Every Sunday @10:00 PM:

Index Optimization of all AVAILABILITY_GROUP_DATABASES
Shrink all AVAILABILITY_GROUP_DATABASES log files.

Please correct my thinking before I get in a trouble. Because even transaction log backup does not help us anyway as the space is keep on increasing.

What should be the course of action in this case apart from shrinking log file (as it not recommended).

Please any expert advice is highly appreciated.

Thanks for your help.


texts: Count values from one table using two columns in another table

texts:
I have a problem. I have table_1 (col1, col2, value) and table_2 (col1, col2).

I want to count values from table_1 but only the rows which exist in table_2. In both tables col1 and col2 are key (not primary).

I've tried:

select col1, col2, sum(value)
from table_1
where table_1.col2 in (select col2 from table_2)
 group by col1, col2 ;

It works , but wrong, select too many rows from table_1


texts: SSIS 2012: What rights are needed to connect to SSDB via DMO

texts:
I'm trying to connect to the SSDB catalog to execute a package using the .NET object model e.g.

using Microsoft.SqlServer.Management.IntegrationServices;

var sqlConn = new SqlConnection(connectionString);
var ssis = new IntegrationServices(sqlConn);

var project = ssis.Catalogs["SSISDB"].Folders[package.Path].Projects[package.Project];
var pinfo = project.Packages[package.Name + ".dtsx"];
var id = pinfo.Execute(false, env, packageParams)

If I run this I get an error

The server principal "XXXX" is not able to access the database \"SSISDB\" under the current security context

If I grant the user sysadmin rights it works, so it's definitely a permission issue, but I can't use that as the solution.

Under the old Integration Services model you would make the user a member of db_ssisadmin, db_ssisdtuser or db_ssisoperator - tried that and it doesn't help :-(


texts:
I am new to psql and am trying to set it up using the Postgres.app site on my Mac. After installation and opening psql, this is the message I get:

  Last login: Sat May 21 07:10:21 on ttys000
  '/Applications/Postgres.app/Contents/Versions/9.5/bin'/psql -p5432
  MacBook-Pro:~ MYUSERNAME$ '/Applications/Postgres.app/Contents/Versions/9.5/bin'/psql -p5432
  psql: FATAL:  role "MYUSERNAME" does not exist

Using the troubleshooting section I used to the server manual for more information on the issue. This is the result:

  MacBook-Pro:~ MYUSERNAME$ /Applications/Postgres.app/Contents/Versions/9.5/bin/postgres -D "/Users/MYUSERNAME/Library/Application Support/Postgres/var-9.5"
  FATAL:  lock file "postmaster.pid" already exists
  HINT:  Is another postmaster (PID 1813) running in data directory "/Users/MYUSERNAME/Library/Application Support/Postgres/var-9.5"?

Has anybody got any suggestions how I could proceed?


texts: ORA-28031 while attempting to rebuild or drop index

texts:
I am in the process of using Data Pump to setup a copy of an Oracle database from 11.2.0.3.0 on Windows to 11.2.0.4.0 on Oracle Linux. I have had a few issues with invalid objects which I have managed to resolve but I also have a number of indexes with the domidx_opstatus of 'FAILED'.

Attempting to rebuild these indexes results in the error:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine

ORA-28031: maximum of 150 enabled roles exceeded

This is a large database that I have recently begun working on so would like to avoid removing roles from any users to avoid breaking any fuctionality.

Is there away around this or another way of fixing the indexes?


texts: Why won't pg_basebackup output go to log?

texts:
I run a nightly pg_basebackup from a cron job (Postgres 9.3 on debian "wheezy"), but the backup has been failing. It actually creates output, but the .tar file is corrupt and won't extract. I went to my log file, only to find it is 0 bytes. Could anybody tell me what is wrong with my setup? Why isn't the log file saving the output of pg_basebackup?

#!/bin/sh
PATH=$PATH:/usr/bin
export PATH

BASEBACKUP_LOG=/var/log/postgresql/basebackup.log
[ -f $BASEBACKUP_LOG ] && mv -f $BASEBACKUP_LOG $BASEBACKUP_LOG.old

BACKUP_PATH=/path/to/backup/$(date +%F)
pg_basebackup -D $BACKUP_PATH -Ft -z -v 2>&1 | ts '%F %T %Z' &> $BASEBACKUP_LOG

The way I'm reading the pg_basebackup line, I think it should redirect stderr to stdout, pipe the combined stream to the ts function (prepend timestamp to a line) and write that to the path at $BASEBACKUP_LOG. Am I missing something obvious here?

On advice given below, I added an explicit reference to the path for ts & pg_basebackup (both reside in /usr/bin). There was no change in behavior.


texts:
I have just downloaded a large xml file and most of characters appear as "�". I changed the encoding but there was no improvement. Anyone knows what this might be due to?

One fishy thing: the file does not start as a usual xml, but rather as: "� m�Y �/opt/cds-invenio/var/www/dumps/.HepNames-records.xml"

The administrator give me a tip: "clearly it's Content-Type: application/x-gzip", which I did not understand.

Thanks for the support.


texts:
I'm testing knowage system, my backend is MSSQL while knowage server is my sql.
I found on dataset audit log this query:

select "ATC_1" AS "ATC_1",  SUM("Importo_Scarto") AS "Importo_Scarto", SUM("Q_Scarto") AS "Q_Scarto"
from (SELECT
      [ATC_1]
      ,[MESE]
      ,[ANNO]
      ,[Q_Scarto]
      ,[Importo_Scarto]
  FROM [dbo].[MyTable]) T
  where (("ANNO","MESE") IN ('2017','10'),('2017','11'),('2018','01'),('2018','02'),('2018','03')
  and ("Invio") IN ('3'),('1'),('2') )
  group by "ATC_1"

the error I get is this:

  com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near ','


texts: I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is updated which will subtract the number in counter 2 from the number in counter 1 and put the results in count column 3? I would want to index count column 3 for queries.


texts:
Our work involves updating products, we have a large table of products, and the prices and other pertinent information gets updated hourly. Let’s say it’s an amazon store, and we are talking of amazon products, and we have to update the selling price, buy box price etc. we pull the information hourly from amazon into our program and update the data in database.
My workflow is, pull all the products from the database into the program, (we use C# and EF Core), update the relevant products, and send back the updates to the database.
This way has the penalty of reading a lot of info from the database into the program, but I feel this is very efficient, because EF Core has change detection, so even if I assign all products with the price of the incoming products, if there is no change EF core will not change anything, it will only generate an update statement for those products which have changed information.
Also, it does not make large update statements, it generates small, targeted update statements, like
update products, set BuyBoxPrice = 12.23 where productid = 23345.
I am working with a very gifted developer, who is very natural with SQL, he argues that this way is wrong, I should rather put all the incoming info in a temp table named #products, and send it into the database, and then run a stored procedure which should do like this,
update products, set BuyBoxPrice = #products.buyboxprice from products inner join #products on products.produtid = #products.productid.
So this approach avoids having to make large reads from the database.
I am not so experienced, my question is, do reads create locks or degrade the database performance, probably yes?
Now here are the reasons I am not comfortable with his approach.
It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.
My colleague counters, that I can remedy that by adding a where statement, like
update products where products.buyboxprice <> #products.buyboxprice
I don’t think that this reduces the penalty u pay, I think its still the same effect.
Another major worry is that large updates create locks, for that alone it should be avoided. Now of course I can break up the updates into chunks of less than 3000 etc.
Third point, when SQL has tummy trouble, it throws symptoms all over, and strange things start to happen, customers call in yelling and boss gets angry, and I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.
So my question is, who is right, is it more performant to do the updates via reads and EF core, or via SQL


texts: sp_msforeach db - Why do we need to use the USE keyword

texts:
sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?


texts:
In my product we need to create a solution for the reporting team to extract and process the data. We need to send them incremental data changes from our DB. I am evaluating various solutions. Looked into CDC and Replication and not sure which one to use or to build a custom one.

While CDC is easy to configure and need lesser infrastructure than replication, but the change tables are in the same DB the performance may slowdown depending on how frequently the reporting team queries the db.

Replication seems more salable but it needs lot of infrastructure. Also looks like there has to be separate instance of the DB in the same server or use another server.

Will a custom solution help here? Is there a way to create a copy of the database and read the logs from source DB and push incremental data to a destination DB through a job.


texts: Moving mysql datadir cause info: checking for tables which need an upgrade

texts:
So after moving the data directory to another partition, I get this error:

[info] checking for tables which need an upgrade, are corrupt or were not closed cleanly.

This is on Debian 7.5.

The steps I took to do this:

service mysql stop
nano /etc/mysql/my.cnf
changed datadir=/data/mysql
cp -pr /var/lib/mysql /data/
service mysql start

When I did a mysqlcheck, all tables in mysql db say OK. What else am I missing to get rid of this error?


texts: SQL Server 2008R2 Cluster: Spread tempdb over several files AND shrink original single file

texts:
On my SQL 2008 R2 active/passive cluster, I've received an error message regarding I/O requests on tempdb.mdf taking longer than 15seconds. Googled that and it as suggested I spread tempdb over several files. I have 2 4-core CPUs and figured I'd spreaad tempdb over 8 files.
The current tempdb.mdf is 8GB in size, most of which is unused space. I figured I'd start out with all 8 files being 512MB with filegrowth set to 64MB increments.

Ive executed the T-SQL to create the extra files and set the original tempdb.mdf's initial size to 512MB. My question is:

If I now rollover the cluster to the other node, will the tempdb.mdf file shrink to 512MB?
And will this allow the SQL server to spread tempdb across all 8 files?

Or must I perform any other operation?

Or - hope this is not the case - have I made a mistake that means the SQL server will crash when I rollover ?


texts: Help with Replication from an Always On Availability Group

texts:
I have a two node SQL Server 2016 Multi subnet Availability group. I am trying to setup transnational replication from the AG to a standalone instance for reporting, which is also acting as a remote distributor. We only need a small subset of the tables so adding the standalone instance as a third replica isn't an option.
I've followed the Microsoft Guide for configuring this. I've run the EXEC sys.sp_redirect_publisher and when I run EXEC sys.sp_validate_replica_hosts_as_publishers it returns the AG listener. But when we failover the AG the replication stops working with the following error reporting in replication monitor

Error messages: Unable to query the redirected server
'AGL' for original publisher 'PUB1' and publisher
database 'AlwaysOnTest' to determine the name of the remote server;
Error 7303, Error message 'Error 7303, Level 16, State 1, Message:
Cannot initialize the data source object of OLE DB provider
"SQLNCLI11" for linked server
"[B66E8E27-E422-41DB-BBBC-254D450468D7]".'. (Source: MSSQL_REPL, Error
number: MSSQL_REPL21879) Get help: http://help/MSSQL_REPL21879 Errors
were logged when validating the redirected publisher. (Source:
MSSQL_REPL, Error number: MSSQL_REPL22037) Get help:
http://help/MSSQL_REPL22037

Couple of things I've checked and tried

Checked linked servers for the subscriber exist on all secondary replica hosts.
Switched the security mode to SQL auth using sp_changedistpublisher based on this.

When the AG is failed over if I re-run on the distributor EXEC sys.sp_validate_replica_hosts_as_publishers it returns

Unable to query sys.availability_replicas at the availability group
primary associated with virtual network name 'AGL' for the server
names of the member replicas: error = 7303, error message = Error
7303, Level 16, State 1, Message: Cannot initialize the data source
object of OLE DB provider "SQLNCLI11" for linked server
"[7A7DA613-BFDA-4C51-B62C-9C7CD94E769E]"..',

I am stuck what to try next....help please!!!
UPDATE 16/06/2020
When the AG is failed over I tried connecting to the AG listener from the remote distributor. SSMS connected fine, but when I tried connecting with SQLCMD it returned an error. Also I can connect to the failover instance directly from the distributor, bypassing the listener.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes
Provider: Could not open a connection to SQL Server  2. . Sqlcmd:
Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout
expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A
network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and i f SQL Server is
configured to allow remote connections. For more information see SQL
Server Books Online..


texts: When i select table with view/edit data => all Rows then i try to refresh table for newly updated data in table from object tab. it seems it's not working.this works perfectly in pg-admin 3 but in pg-admin 4 it seems it's not working at all.is this an issue in pg-admin 4 or i configured something wrong when i installed i followed all the instructions in docs on how to install it on Ubuntu exactly.can someone help me on this matter or explain it ?


texts: postgreSQL default collation (case insensitive)

texts:
I am using PostgreSQL 12 and I would like to create a schema with insensitive collation. I read about Nondeterministic Collations here https://www.postgresql.org/docs/current/collation.html
When I create a new Database I always get the collation German_Germany.1252. This is not listed in pg_collations. I tried to create a new collation and use it like this
CREATE COLLATION german FROM "de_DE";
create database test2 LC_COLLATE german;

but the new database is also created with German_Germany.1252. I read that PostgreSQL use the collations provided by the operating system. I am running on windows and by executing the command line systeminfo, I get
System Locale en-us;English (United States)
Input Locale de;German (Germany)

So I assume that German_Germany.1252 comes from my windows, is there a way to change it to be nondeterministic or case-insensitive?
In generla is there any way to set a case-insensitive collation in PostgreSQL?


texts: Is it Possible to Recover Data from a BLACKHOLE table?

texts:
I had created table with engine BLACKHOLE  basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result.

I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam. but i had tried that also but unable to get the result. Can any one pl help me on this issue to fix it.

mysql> CREATE TABLE test1(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test1;
Empty set (0.00 sec)

mysql>  CREATE TABLE test_recovery as select * from test1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_recovery ENGINE = innodb;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_recovery;
Empty set (0.00 sec)


texts: Statistics. Are multicolumn histograms possible?

texts:
I am thinking of a situation where I have two columns with high density but these columns are not independent.

Definition

Here it is the definition of the table that I have created for testing purposes.

CREATE TABLE [dbo].[StatsTest](
    [col1] [int] NOT NULL,  --can take values 1 and 2 only
    [col2] [int] NOT NULL,  --can take integer values from 1 to 4 only
    [col3] [int] NOT NULL,  --integer. it has not relevance just to ensure that each row is different
    [col4]  AS ((10)*[col1]+[col2])  --a computed column ensuring that if two rows have different values in col1 or col2 have different values in col4
) ON [PRIMARY]

Data

The data for the experiment is the following

col1    col2    col3    col4
1       1       1       11
1       2       2       12
1       2       3       12
1       3       4       13
1       3       5       13
1       3       6       13
1       4       7       14
1       4       8       14
1       4       9       14
1       4       10      14
2       1       11      21
2       1       12      21
2       1       13      21
2       1       14      21
2       2       15      22
2       2       16      22
2       2       17      22
2       3       18      23
2       3       19      23
2       4       20      24

Step 1: Filtering by col1

SELECT * FROM StatsTest WHERE col1=1

As expected the Query Optimizer guesses the exact number of rows.

Step 2: Filtering by col2

SELECT * FROM StatsTest WHERE col2=1

Again we have a perfect estimation.

Step 3: Filtering by col1 and col2

SELECT * FROM StatsTest WHERE col1=1 AND col2=1

Here the estimation is far from being close to the actual number of rows.

The problem is that the query analizer implicity assumes that col1 and col2 are independent but they are not.

Step 4: Filtering by col4

SELECT * FROM StatsTest WHERE col4 = 11

I can filter by col4 = 11 to get the same results as the query in Step 3, because col4 is a computed column and according to the way it has been defined col1 = 1 and col2 = 1 is equivalent to col4 = 11
Here, however, as expected the estimation is perfect.

Conclusion/Question

¿Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns?
¿Is the computed column and the filter by the computed column estrictly neccesary for obtaining actual precision?

Example in sqlfiddle


texts: How can I select using the same fields from an undetermined number of tables using MySQL?

texts:
I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:

foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
foo_referencez

The tables all contain the fields foo_id, bar_id and bar_weight. I need to get the top five bar_id fields ordered by bar_weight (descending) where foo_id equals something from each table.

The problem is, no two servers will have the exact same number of tables, or the same name. However, they always start with foo_reference. I need to run this in dozens of places. In an ideal world, there would just be one properly indexed foo_reference table, unfortunately changes just aren't possible.

Initially, I tried just using SHOW TABLES LIKE 'foo_reference%' in a sub query in order to build the list of tables that have to be queried. Apparently, MySQL does not like that, so I just queried the information schema directly:

select bar_id, bar_weight from
(
    select table_name as name
    from information_schema.tables as tmp
    where tmp.table_name like 'foo_reference%'
) as res
where res.foo_id = '1'
order by res.bar_weight desc
limit 0, 5;

MySQL is telling me that bar_id is an unknown column in the field list. When I run the sub query by itself, it returns the list of tables that need to be queried.

What am I doing incorrectly? All I want are the top 5 bar_id fields from each table where foo_id is a certain number. As you can tell, I'm doing a bit of learning (and quite a lot of guessing) as I go here.


texts: Deadlock error in inserting bulk of records to MySQL database using trigger and Qt multi threading

texts:
I have a multi thread program (In this case in Qt) which every thread at a specific time, inserts 500 records in a MySQL table (I call that as A_tbl) and also there is a trigger for A_tbl that inserts some values to other tables (I call them as B_tbls) and after that by some select queries in the trigger, I get the IDs of those inserted records in B_tbls. (I think that I have to use these select queries and last_insert_id() is not useful in my case since maybe I need the ID of the first inserted record).

But these select queries along with the last insert query lead to this error:

Deadlock found when trying to get lock; try restarting transaction QMYSQL3: Unable to execute statement

This is my trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `IpAn`.`A_tbl_BEFORE_INSERT` BEFORE INSERT ON `A_tbl` FOR EACH ROW
BEGIN

INSERT IGNORE INTO source_names (source_name) VALUES (NEW.source_name);
INSERT IGNORE INTO locations (loc1,loc2) VALUES (NEW.loc1,NEW.loc2);

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);
SET @locations_id = (select id from locations USE INDEX (PRIMARY) where loc1=NEW.loc1 and loc2=NEW.loc2);

INSERT IGNORE INTO sections (source_names_id,locations_id) VALUES (@source_names_id,@locations_id);

...

END

If I change the threads to one, the error will not occur.

How can I solve this?


texts: Database design for voting module with long-run and high-load capability

texts:
I'm currently designing a project and I need professional advice from DBA.

My project will feature voting system somewhat similar to the one used in stack exchage websites. I have users and content pieces and users can vote for content pieces they like or don't. Note, that I will have vote up/down option on the feed list, so if I load 30 content pieces I will need to also load the data of user's votes on every piece, since the up/down button should be highlighed if user already has voted for a specific piece. In other words, I expect big load on the votes table. I was thinking of the basic structure like this:

Table users (user_id, ...), table content (content_id, ...), table votes (vote_id, user_id, content_id, datetime, vote). However, I have doubts about this design.

Let's say I have 10k users and 1k content pieces. That's up to 10 million records in table votes. If I start thinking about scaling it up, I can imagine a big problem. Content doesn't go anywhere, old votes as well, so the longer website runs, the more records will be in the table and the slower it will work.

Let's say in some years I will have 100k users and 20k content pieces. That's up to 2 billion records. I understand that not every user will vote on every content piece, but the problem however is clear - there is a limit to that design (by limit I mean that select query will be slow when the amount of rows will reach some point).

So my questions are:

Is there really a limit to that design and if yes, how can it be dealt with?
1.1. If the select query on votes table will be getting slower, what can I do to speed it up?
Is there a better way to design this kind of relations?
How do I cache that data? Or is that even needed with proper indexing?
What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id,  content_id)?
Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

I really would like to do things right from the start so in a few years I won't end up with a slow website. Thank you for your time.


texts: ORM-style server-side programming languages (OO replacement for PL/SQL?)

texts:
Good evening,

Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?

(for MySQL, PostgreSQL, Oracle  or etc.)


texts: MongoDB/Mongochef: import from JSON changed dates to string

texts:
I have a huge JSON file (about 5 GB) with several fields in all rows.
this is what one document looks like:

{
    "_id" : ObjectId("55f0105346c5110dfc6b8760"),
    "patient" : "198",
    "concept" : "Ampicillin",
    "confidence" : NumberInt(100),
    "value" : 1.0,
    "time" : ISODate("2007-06-27T17:24:00.000+0000"),
    "stay" : "20220"
}

This is data from hospital stays.
For some reason, when I imported the JSON file to MongoDB (using mongoimport in linux command line), the 'time' field was saved not as ISO-date, but as a string.
I figured - "no problem" and using mongo-chef altered the field to be of "Date" type using the 'Edit Value' option. I then picked the override option for all documents matching criteria.

What happened then is that the entire collection got the exact same value in the "time" column.

So my question is - either - (A) how can I import it correctly, where mongoimport inserts the "time" as ISOdate, OR, (B) how can I correctly change it in mongochef, after the import.
This is time sensitive so PLEASE if anyone has any idea - help!

Thanks!


texts:
Does anyone know how to restart a SQL Server Managed Instance?

The stop / start / reset are all grayed out, so that’s really. It an option.

Any thoughts?

Thanks...


texts: Transaction publication with merge publication

texts:
I have a transaction publication setup on SQL Server 2014 lets call it (node A) that replicates to SQL Server 2008 (node B), then I use merge replication to synchronize changes from (A) through (B) to a SQL Server CE database on a Win CE devices because (A) doesn't support merge replication to compact framework.

The problem is that changes came from the transaction publication (A) isn't replicating through merge publication (B) into the SQL Server CE database on the Win CE device while if I change anything in (B) database, it's replicating just fine.
Am i missing anything?

Please any help would be appreciated.


texts: Doing a insert across two databases with same collations and getting an error

texts:
SQL query :

insert DB1.dbo.tbl1

select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A'
from DB2.dbo.tbl1 T2
where T1.Number not in (select distinct AccountNumber from DB1.dbo.tbl1)

Error  message :

  Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


texts: Naming Convention for SQL AD Group Logins (Potential Bug)

texts:
My team is attempting to come up with a new naming standard for our Active Directory groups within SQL Server to help manage permissions.
DOMAIN\ENVIRONMENT_DATABASE_FUNCTION
EXAMPLES:
TEST\STAGE_StackOverflow_RW
TEST\STAGE_StackOverflow_RO
TEST\STAGE_StackOverflow_EX
However, before implementing this convention we decided to look up character limitations of both active directory and SQL Server Logins and while the character limit itself doesn't appear to be a direct issue, we did stumble upon this article from 2014: Max Length of AD Names for Security
Tl;dr of that forum post:

My team and I used one of our dev servers to attempt to recreate this potential issue, but realized that it was very difficult to test as the permissions on AD groups sometimes don't propagate for hours even if you restart the server (tested this last night and proved it).  I am wondering if anybody knows if this issue has been resolved on SQL Server 2016/2017.  Is it safe to use the naming convention I posted at the very top where the first 20 characters are the same or do we have to come up with a way of abbreviating all of our objects so that the first 20 characters of a login are NEVER the same.


texts: Database Design Regarding a Relationship for Patient Visit

texts:
I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.

Busines rules

There are a total of 6 different studies, and patients can be categorized under more than one study if they fit the criteria.
Once they are under a study, they are required to pay routine visits for various type of exams (a total of 10 different kinds), i.e. clinical exams, physical exams, and other examinations to keep track of information.
Each of these various exams must be time stamped to allow back tracing.
In addition, patients are required to complete these exams up to three times while in the study, and each examinations has 10+ parameters that needs to be recorded as well.

So lets say there are 100 patients, these patients are categorized into studies A, B, C, D, E, F, with the possibility of being in more than one studies at a time (i.e. patient_25 can be in studies A,B, and E). Each study requires 3-8 different patient visits to collect information from examination, and some studies require the examinations to be performed up to a total of 3 times over a period of time.

What would be the best way to create tables that fit this kind of structure?
Here's my thoughts so far, let me know if there is a better way.

My representation with tables so far

Tier 1.

Studies Table: study_id(primary key), study_name, study_patient_code, patient_id(foreign key)

Tier 2.

Patient Demographics: patient_id(primary key), patient_name, patient_gender, patient_dob...etc.

Tier 3.

Visits Table: visit_id (primary key), patient_id (foreign key), visit_date, visit_type

Tier 4.

Physical Exam: physicalexam_id (primary key), visit_id (foreign key), pe_height, pe_weight, pe_score...etc.
Clinical Exam: clinicalexam_id (primary key), visit_id (foreign key), parameters....etc
Examination 3: exam3_id (primary key), visit_id (foreign key), parameters....etc
Examination 4: exam4_id (primary key), visit_id (foreign key), parameters....etc

.....

Examination 10: exam10_id (primary key), visit_id (foreign key), parameters....etc

So basically, the study table will be linked to patient demographics table. the patients table will be linked to visits, which will then be linked to various examination tables with columns consisting of the parameters recorded on the date of visit. Is this a viable method of approaching this problem? Or are there a more elegant way of doing so?

I hope this makes sense, if not, let me know and I'll be sure to elaborate more.

Additional information

I apologize for not doing a good job of explaining myself. So there are various type of exams that returns on average about 15-20 fields of data that needs to be stored. Each exam has its own purpose so the parameters will be different. For example, one exam may be just for Lumbar Puncture and contains fields with specific information with regards to that, physical exam will be for height, weight, blood pressure, BMI...etc. Patients may have to comeback for an exam multiple times during a study (for example, need to do physical exam on 1st, 2nd, 3rd visit.)

I guess what I'm trying to ask is where should the parameters of each exam be stored? and where should the time stamp be stored? How should I organize the tables so that we normalize the database for each visit without excessive repetition of information for different exams and the 1st, 2nd, 3rd visits?


texts: Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

texts:
I'm getting the following error when I try to use the FORCESEEK hint with the query below:

  Msg 8622, Level 16, State 1, Line 96 Query processor could not produce
  a query plan because of the hints defined in this query. Resubmit the
  query without specifying any hints and without using SET FORCEPLAN.

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

IndexedView1 and IndexedView2 are both schemabound indexed views, and there's a unique clustered index on both views on the fields IndexedField1, IndexedField2.
Not sure if relevant, but the clustered index is 40 bytes big (per row).

Here's an example of exactly what both views look like under the hood:

SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

Nothing too fancy, and no hints inside the view's query.

This is the estimated execution plan (without trying to use a query hint):


texts:
I need to add a windows user and set role to sysadmin for a bunch of linked servers and cannot figure out the syntax to make the current linked server active for the query to add the user and role.

Any suggestions?  I have only seen one example of this on the web but it does not work for SQL 2008.


texts: How can I restore a MySQL database after formatting my hard drive

texts:
I recently formatted my PC which had MySQL running on it.  As a result, I lost the databases on my hard drive.

I tried to restore the databases by re-installing the MySQL Server and putting the databases back into the MySQL datafiles folder.  When I started the MySQL service, I got error 1067: Windows could not start the MySQL Service on local computer.

If I just install MySQL and do not put my databases into the datafiles folder, I don't get the error.

How can I successfully restore my databases?

I'm using Windows Server 2008 R2 enterprise.


texts: SQL Server: what should be done when a process is blocking another process?

texts:
I'm an accidental DBA and still learning.

When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?

Should I simply kill those processes or is there a way to "release" or maybe preemptively preempt this blocking?


texts: Why store XML Schema in SQL Server 2012 database?

texts:
What a real world production environment example for storing and or using a XML schema in a SQL Server database?

  I am working my way though the Microsoft 70-461 exam objectives. I have
  completed the Microsoft walk through for creating and storing an XML
  schema in the database at
  https://technet.microsoft.com/en-us/library/ms176009(v=sql.110).aspx .

  I have yet to work in a database environment where the application
  will access a XML schema that is stored in the database. What is a
  real world use case for creating and storing a XML schema in a
  production SQL Server database?


texts: Can I tell if a stored procedure has executed against a particular database?

texts:
As a result of a release a database is behaving "unusually".   I suspect a stored procedure was missed during a recent release, however my DBA is telling me 100% it was executed.

Is there any way to definitively prove it was or wasn't?


texts:
I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1).

Is there a way for me to cascade this soft delete from a parent to a child? A built in command? or is up to me to write the code?

I am thinking that what I am doing is in fact an update. So I'd be using an ON UPDATE CASCADE, but will that update the same columns on the FKs? or just the primary key?


texts: How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

texts:
I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:

42, 1
43, 1
42, 2
43, 5
43, 6
42, 3

the output contains the client_id, and boundaries of the gaps, like this:

43, 2, 4

Note that there's no row with client_id=42 because there are no gaps in its data.

I've tried this for the core part of the query:

select
    client_id,
    order_no as start_order_no,
    lead(order_no) over (order by client_id, order_no) as end_order_no

but that apparently doesn't do what I want, and I suspect it's because of client_id in the OVER part.


texts: Get all data from two tables and group it into different category in mysql

texts:
These are my tables to build an FAQ:

CREATE TABLE `tbl_section` (
    `section_id` int(11) NOT NULL AUTO_INCREMENT,
    `section_title` varchar(100) NOT NULL,
    PRIMARY KEY (`section_id`),
    UNIQUE KEY `section_id_UNIQUE` (`section_id`),
    KEY `section_id_idx` (`section_title`)
)
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `tbl_topic` (
    `topic_id` int(11) NOT NULL AUTO_INCREMENT,
    `section_id` int(11) NOT NULL,
    `topic_title` varchar(500) NOT NULL,
    PRIMARY KEY (`topic_id`),
    UNIQUE KEY `topic_id_UNIQUE` (`topic_id`),
    KEY `section_id_idx` (`section_id`),
    CONSTRAINT `section_id` FOREIGN KEY (`section_id`) REFERENCES `tbl_section` (`section_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

CREATE TABLE `tbl_faq` (
    `faq_id` int(11) NOT NULL AUTO_INCREMENT,
    `topic_id` int(11) NOT NULL,
    `faq_title` varchar(500) NOT NULL,
    `faq_description` varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
    `faq_views` int(11) DEFAULT '0',
    PRIMARY KEY (`faq_id`),
    UNIQUE KEY `faq_id_UNIQUE` (`faq_id`),
    KEY `topic_id_idx` (`topic_id`),
    CONSTRAINT `topic_id` FOREIGN KEY (`topic_id`) REFERENCES `tbl_topic` (`topic_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=latin1

I have some how come up with a query:

SELECT a.topic_id as tid, a.topic_title as topic,
    (SELECT b.faq_title FROM tbl_faq AS b WHERE b.topic_id = a.topic_id LIMIT 1) as question,
    (SELECT b.faq_description FROM tbl_faq AS b WHERE b.topic_id = a.topic_id LIMIT 1) as answer
    FROM tbl_topic AS a
    WHERE a.section_id = 1;

When executed it works but gives only the first row of each TOPIC (I deliberately put that LIMIT 1 just to see if code is working)

Actually I need all question-answers with their respective TOPIC in rows which should look like :

TOPIC HEADER ONE

Question One
Answer One

Question Two
Answer Two

TOPIC HEADER TWO

Question Three
Answer Three

Question Four
Answer Four
and on...

Hope I have clarified my stand.


texts: Best approach for UNIQUE ( not UUID ) primary key for a mult tenant with schemas on Postgresql

texts:
I'm working on some projects and I always have this doubt and I don't know if I'm doing it in the wrong way or in the right way.
I have 2 new applications one is a MarketPlace and the other is a SAAS software. For both, I will need to make a global search feature on the application, so I make a multi tenant with schemas on PostgreSQL where each of my clients will have a schema like:
master
public
tenant1
tenant2
tenant3
and goes on...
I have a table called product when each of my clients will store a product and i want to make a global QUERY for each of the product tables from all my tenants.
I use Inheritance from PostgreSQL following this topic:
https://stackoverflow.com/questions/20575610/select-retrieve-all-records-from-multiple-schemas-using-postgres
And now all is working fine but with one problem, I make a simple sequence for all my schemas and I need to make it GLOBAL. So I using 2 approaches to make this work
APPROACH #1
I store in each tenant table the tableoid and I make a JOIN with tableoid and primary key
SELECT  tableoid, tableoid::regclass, * FROM master.bra_product LIMIT 10

APPROACH #2
I create a GLOBAL SEQUENCE on public schema on PostgreSQL for each new tables on the database
CREATE TABLE tenant3.bra_product (
        product_id integer DEFAULT nextval('public.product_global_seq'),
        description varchar
    );

APPROACH #3
Create a composite primary key with SEQUENCE + UUID ( this for me I have a bad experience with UUID my QUERIES take so long to retrieve ), that if I need to make a tenant select I use SEQUENCE if I need a global I make a JOIN with UUID
Please give some light to move on


texts: querying multiple values from one column

texts:
we have a table called transaction_details.
when i write this query

SELECT
DESCRIPTION,
item,
cust_code,
pack_description,
uos,
pack,
total_value,
total_value_vat,
total_value_goods,
price_indicator
from TRANSACTION_DETAILS
where cust_code=1273
and item =371355
and item =371476
and item =371629
and item =370556
and item =370677
and item =370820
and item =370952
and item =371070
and item =371223
and item =372154
and item =372253
and item =371751
and item =371872

it shows me nothing in the result pane. we want to see the reult of these all the item one by one in single line of each item and dont want to be duplicate. thanks for helping.


texts:
Using PostgreSQL 9.4, is there anyway that we can specify the tsquery to match all?

I have tried:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery;

But it doesn't seem to work. It returns false.


texts: Mysql fulltext index search returning weird result

texts:
I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure.

Table(ingredient) - Applied fulltext index on ingredient column.
------------------------------------------------------
 ingredientID   rcteID  ingredient
    310           1     Mint Leaves
    311           1     Corriender Leaves
    312           1     GreenChili

I am trying to fetch above record below fulltext search query but not getting that record.

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Mint Leaves +Corriender Leaves +Greenchili' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID`
IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

Why above query is not working for above record?

When I tried below query it worked. just changed searching text.

SELECT `Ingredient`.`ingredientID` , `Ingredient`.`rcteID`
FROM `ingredient` AS `Ingredient`
WHERE MATCH (`Ingredient`.`ingredient`)
      AGAINST ('+Greenchili +Mint Leaves +Corriender Leaves' IN BOOLEAN MODE)
AND `Ingredient`.`rcteID` IN ( 1 )
GROUP BY `Ingredient`.`rcteID`

OUTPUT
--------------------
ingredientID    rcteID
311                1

Don't understand what's going on. Why first query not returning any result and below query returning result?


texts: Is it common practice to mix InnoDB and MyISAM tables on same server?

texts:
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's going to be a slow process, focusing on the most write-intensive tables at first).

Is there anything wrong with running a dedicated server where both types of storage engines exist?


texts:
What command do you use to find the patch level of MySQL?  I am currently on 5.1.73.  I need to find out what patches have been applied to this database.

Thanks...


texts: "Logon failed for login 'sa' due to trigger execution." When No SA Login Triggers Defined

texts:
Last night, I received some error messages in my SQL Server instance stating:

"Logon failed for login 'sa' due to trigger execution."

The instance has a number of logon triggers enabled, but after verifying them, none of them would reject the 'sa' login. The logon triggers that exist are all in this format:

CREATE TRIGGER [forbid_bar_connection_for_foo]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'foo' AND APP_NAME() like 'bar%'
    ROLLBACK;
END;
GO

and they exist as a stop-gap until we can get proper security in place. (I know that this is insanely easy to bypass.)

In none of the triggers do we check ORIGINAL_LOGIN() against 'sa'.

Around the same time that I saw this for the first time (followed by many of the actual logon triggers firing, far more than they should've), there appears to have been an out of memory event. A new database was being brought in, and there's a stack dump preceding this by about 30 seconds regarding a non-yielding IOCP Listener.

My assumption is that there was a memory issue at play, and this caused the logon triggers to not fire correctly, and thus rollback access to sa. Is this reasonable? Is there another explanation for a logon trigger not allowing a login that it does not check against?

Thanks for any insight!


texts: Force drop db while others may be connected

texts:
I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB.

How can I implement it?

I'm using dropdb currently, but other tools are possible.


texts:
I have a live server and a report server. The live server, where the application is running, has many indexes. When we create replication all the indexes are also created on the report server.

After 2 months, disk space on the report server is running out, and I have to make some space. I checked all the unused indexes on the report server and found many indexes that have not been used any time in the past two months. But they are required for the live server.

Can we delete those indexes that take unnecessary disk space on the report server as no application is pointing to report server? Will it be ok? I want to be sure before I make the changes.


texts: Why does a fresh SQL Server instance with the same details as the original think that my DB backup is in the original location?

texts:
So I'm working on a server application for a large government body and need to setup a development environment to test on. For this I'm using a Vagrant-controlled VM with Windows Server 2019 installed as the guest OS and SQL Server 2019 as the DB engine.

After creating and subsequently destroying the VM, then rebuilding it I am now trying to restore a DB backup to a fresh instance with the exact same instance name, logins etc. But I get this error from my PowerShell script:

PS C:\Users\vagrant> Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"

Invoke-SqlCmd : Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL15.JIRADB\MSSQL\Backup\db-name.bak'. Operating system
error 2(The system cannot find the file specified.).
RESTORE DATABASE is terminating abnormally.
At line:1 char:1
+ Invoke-SqlCmd -Query "RESTORE DATABASE DBName FROM DISK = '.\db-name.bak'"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

What gives?! Clearly the backup file now exists at C:\Users\vagrant but SQL Server seems to think that it should be in the original, default backup location.

Obviously, I can simply move the backup file back to the original location, but I'd like to know why SQL Server is expecting that. MTIA! :D


texts: When converting a table valued function to inline, why do I get a lazy spool?

texts:
I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a function parameter as the key), then runs one or more of four different insert statements (differing only by the WHERE clause) to populate a temp table based on which of the bit variables have a value of 1, and then returns the content of the temp table.

I rewrote it to be one SELECT (with a CTE to get the four bit column values) using four OR clauses in the WHERE to get the same result.

However, the plan cost jumped from .003 to over 2.5. On the other hand, the actual performance is better judging by the output from SET STATISTICS TIME ON. 75% of the cost of the inline version is for a lazy spool. Is there a good way to avoid the lazy spool and improve performance even more? The output could be anywhere from one row to thousands.


texts: MySQL: Moving Database from Windows to Linux

texts:
I have a Windows based dedicated server have below configuration:

Windows edition: Windows Server 2008 R2 Standard Service Pack 1
Processor: Intel(R) Xeon(R) CPU X5460 @3.16GHZ 3.16 GHZ (2 Processors)
Installed Memory(RAM): 8.00 GB
System type: 64-Bit Operating system

Even, I am facing bad performance of my application. DB connections become high >100 and system starts running slower. It become dead when DB connection reached to around 200. While we defined MAX connection=800. I already tried multiple things like -
MySQL configuration variables tuning
Identified and improved the bad performing queries

Above steps helped a little bit but still facing very bad performance. It is just like I am swimming in the ocean without any expertise and life saving boat.

Now, I am planning to upgrade MySQL from 5.1 to 5.6 as well as switching from Windows to Linux. I guess as Open source - Linux  must have multiple tools to measure performance and tuning tools for Windows. But, before this I want to be sure about below things:

How much will it be useful in performance improvement?
My application is running in dedicated Windows server. Switching application servers also required?
Need to tune MySQL configuration variables again?
What should be the configuration (hardware and software) of LINUX based system?
What performance measurement and tuning tools are available for LINUX?

Many Thanks :)


texts: Does using lower Isolation Level improve OLTP performance?

texts:
We have a SQL Server 2008 R2 instance which gets millions of rows of data per day from automatic devices such as CTI and AVR. On the same instance there are many heavy reports which analyze the data.

I'm looking into the option of separating these activities into two distinct processes:

Getting the rows from the automatic devices.
Analyzing the data and producing heavy reports.

I plan to try to set the isolation level for the first process to the minimum (read uncommitted) as data integrity is not such an issue.

Would I get significant performance improvement from both the separation and usage of the lowest isolation level?


texts: Choice of clustered key when almost all columns would be part of a unique index anyway

texts:
I am creating the following table

CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS
    (
    KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(),
    EmployeeGuid uniqueidentifier NOT NULL,
    PracticeVid smallint NOT NULL,
    ShelfGuid uniqueidentifier NOT NULL,
    CONSTRAINT PK_COMM_SHELF_BLOCK_ACCESS PRIMARY KEY CLUSTERED (KeyId)
    )  ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX UX_COMM_SHELF_BLOCK_ACCESS
    ON dbo.COMM_SHELF_BLOCK_ACCESS (ShelfGuid, EmployeeGuid, PracticeVid)

GO

I never use KeyId in my program, it is only for merge replication. The most common query my application will be using this table for will be

--This will be passed in parameters to the SP
declare @practiceVid smallint = 0
declare @employeeGuid uniqueidentifier = 'C413A410-E13E-4647-9D68-2A38FBC45906'

--actual query
select * from COMM_SHELF
where COMM_SHELF.SHELF_GUID not in
    (
        select ShelfGuid
        from COMM_SHELF_BLOCK_ACCESS
        where EmployeeGuid = @employeeGuid
            and PracticeVid = @practiceVid
    )

Is the index structure of my new table appropriate? The big thing on my mind is I am duplicating almost my entire table in that unique index. The other thought I had doing was to structure the table like the following

CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS
    (
    KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(),
    EmployeeGuid uniqueidentifier NOT NULL,
    PracticeVid smallint NOT NULL,
    ShelfGuid uniqueidentifier NOT NULL,
    CONSTRAINT PK_COMM_SHELF_BLOCK_ACCESS PRIMARY KEY NONCLUSTERED (KeyId),
    CONSTRAINT CK_COMM_SHELF_BLOCK_ACCESS UNIQUE CLUSTERED (ShelfGuid, EmployeeGuid, PracticeVid)
    )  ON [PRIMARY]
GO

making those 3 columns I am interested in the clustered key. However this violates the "Keep the clustered key narrow" and the "Keep the clustered key sequential" guidelines I have read everywhere.

Which of these approaches is the correct way to implement this? If neither are correct please tell me what the correct way to approach this is.


texts: How to downgrade Oracle Database from 11gr2 to 10g?

texts: In certain situation i have to downgrade from 11gr2 to 10g but coudn't find right way to downgrade. Can you please give a right direction and possible causes to Database.!


texts:
I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project deployment. There are no error messages, and the deployment appears to go through fine. Checking the initial packages and projects tables indicates that although the deployment has happened (project timestamp gets updated), the latest version of the package code has not really been deployed as the version number on the packages table does not match the latest version from within Visual Studio.

In fact, the issue is exactly as described in this previous post by another member, only there was no update on the resolution on that post. I'm hoping that someone else may have come across this situation.

Why is my SSIS package not getting updated?

Some additional information that may assist:

Right click, Project deployment - this works (we've tried this with another    project, although for the project we're really interested in we cannot do that as not all the packages are ready for deployment at a project level).

Right click, Package deployment - this doesn't work (deployment is reported as a success, but evidently the package has not been deployed).

Calling the ISDeploymentWizard from the command line for the package in question doesn't work either. Again, no errors reported, but nothing deployed.

When using the wizard via Visual Studio, the XML you can save post-deployment reports 'success' at all stages.

Thank you.


texts: What is the correct model for related tables with millions of rows?

texts:
I need to create a question and answer tables that will have millions (maybe billions) of rows.

The current model is:

Question Table
id_question (PK, auto increment)
id_user
question_content
question_date

Answer Table
id_answer (PK, auto increment)
id_question
id_user
answer_content
answer_date

Is this a correct model (considering better query performance)? Should I add the id_question and id_user columns to the primary key?

Thanks


texts: Trying to get password has for a login through PowerShell

texts:
I've trying to get the password hash for a SQL authentication login via powershell.

The code I'm executing is something like this

Invoke-Sqlcmd -Query "SELECT LOGINPROPERTY('$LoginName', 'PASSWORDHASH') as PasswordHash"  -ServerInstance $SourceInstance

This works in that the query executes fine but the output I get is

PasswordHash

{2, 0, 87, 116...}

I'm guessing (probably incorrectly) that this is due to it returning a varbinary, has anyone experienced this and/or fixed it before?

On a side note I'm pretty sure that a script I have which configures tempdb gets similar output.  I'd like to understand not just how to fix it but why it happens too :)

Cheers
Mat


texts: SQL Server Msg 2812, level 16 state 62 line 30

texts:
I want to make automatic backup and restore my database on SQL Server 2012. I have made automatic backup with script in my Windows server.

Now, I want to make auto restore with script. I have found this below script to do that:

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.TRN'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd

The output shows me correct SQL command if you keep PRINT in this query,
but when I change PRINT to EXEC in restore log, it shows me an error:

  Msg 2812, Level 16, State 62, Line 64
  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-0954.trn' WITH NORECOVERY'.
  Msg 2812, Level 16, State 62, Line 64
  Could not find stored procedure 'RESTORE LOG yogamanual FROM DISK = 'D:\shared241\yogamanual-1002.trn' WITH NORECOVERY'.

Am I wrong? Can you guys help me to fix it? I have searched it in Google, but nothing can solve it.


texts:
I was working with MongoDB using Pymongo Libraries. Suddenly MongoDB stopped. I restarted it. After a while it stopped again. So I searched and tried the following repair command:

mongod --dbpath f:/data/db --repair

The process kept running. But I stopped it using the keyboard interruption.
Now When I am trying to use start the server using my configuration, the window is getting flashed on my screen.
I do not understand what might be the problem as I could see anything in my log file.

Here are the trace log:https://gist.github.com/JafferWilson/0c6538a0124112f253c7f8e70bdde15e
Kindly, let me know what I can do so that I can start and get my data again. I have GBs of data on MongoDB, and month of hardwork will be wasted if not getting started. Please let me know the solution, if any.

My mongoDB is on the Windows 8 server.


texts: Best solution to keep a big table synched in 2 DB

texts:
I manage a few tables with more than 1 billion records. Daily, their ETLs run, reading data from flat files and inserting a few thousand records on each of them.

Now I need to keep these same tables also on another DB, and of course they need to have the same data. Data will be transferred using Linked Server.

The easiest way would be to truncate the destination tables, select on source inserting to destination, and then rebuild its index. But I don't like the idea of copying the same data every day.

Another solution I see is to query both source and destination tables, distincting by date dimension, and then copy only records from dates that are missing. I'd then create a monitor to see if the amount of records per day are equal and highlight not matching days.

Is this second solution good enough, or is there a best practice for this situation?


texts:
I am trying to connect to a MySQL database using Tibco Data Virtualization Studio (TDV).

I was able to connect to the database using MySQLWorkbench. I checked my.cnf and skip-networking is not present.

TDV is throwing the following error:

    "xx.xx.xx.xx:3306@" with the supplied connection information.  [datasrc-3961050]
Unable to connect to data source "/shared/Admin Database/Data Sources/CACHE DATABASE/MySQL Cache" at
    "jdbc:mysql://xx.xx.xx.xx:3306/DBNAME".  Make sure the URL information, user name and password
    are correct.  [datasrc-3961025]
Cause: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. [Log ID: 85a492e6-04c4-491a-be42-d9b29eb93472

Any ideas how I can correct this error?


texts: How to Concat Datetime without Losing Second Precision

texts:
What is best way to Concat on datetime column with other fields, without losing/truncate millisecond precision?

Example: We have a column in datetime precision.

1988-02-27 00:00:04.000

We are creating rowhash on CONCAT

select hashbytes('SHA2_256',concat(SalesRecord,SalesDatetime))

The problem when doing Concat, we are losing second values.
You can test here,

create table dbo.SalesOrder
(
    SalesOrderId int primary key,
    SalesRecord varchar(255),
    SalesDatetime datetime
)
select concat(SalesRecord,SalesDateTime) from dbo.SalesOrder
select concat('ABC ',[SalesDateTime]) from dbo.SalesOrder

ABC Feb 27 1988 12:00AM

What is the optimal way to solve this?

Background: Rowhash was causing duplicates by removing seconds, pipe deliminator | not working either.

The following was not working:

declare @testdate datetime = '1988-02-27 00:00:04.000'
select concat('ABC',convert(binary(16),@testdate))
select concat('ABC',convert(varchar(25),@testdate))


texts:
I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation.

I want to update two different datetime columns if the value of a different column changes. This is to record two specific status changes of the row.

Now SQL Server does neither have row level triggers, nor before update triggers. So my understanding is that I need to join insert and deleted to find out if and which status change occurred and then run a "regular" update agains the table being updated.

My trigger currently looks like this:

create trigger ord_status_history_trigger
  on jobs
AFTER UPDATE
AS
  IF ( UPDATE(order_status) )
  BEGIN
    update jobs
      set  transfercomplete = case
                                when o.order_status = 'initial' and n.order_status = 'sent' then current_timestamp
                                else n.transfercomplete
                              end,
           installcomplete = case
                               when o.order_status = 'delivered' n.order_status = 'installed' then current_timestamp
                               else n.installcomplete
                            end
      from inserted n
        join deleted o on o.jobid = n.jobid
        join jobs aj on aj.jobid = n.jobid;
  END

My question is: does this trigger effectively result in two updates on the table?

The first one being the triggering update and the second one being the one executed by the trigger?

Or is SQL Server smart enough to merge that into a single update to the base table (essentially the way it happens with a e.g. a BEFORE UPDATE trigger in PostgreSQL)

Do I create a potential performance bottleneck here?

We could set those timestamps from within the application as well, but it would be nicer if we could delegate this to the database, so we can be sure this is never forgotten.


texts:
I have table like this in my database :

ID (pk,int,not null)

UserID (int,null)

HitDate (datetime,null)

Every time a user has any transacation in my application one hit inserted in this table.

Now my client want to find out how  many new users I have per day from the beginning of application.

As I find out I have to group user by "UserID"  and get the first HitDate, then remove other hit dates for that user. The first one will be the first login into system. Then I count using this method of all user per day.

My problem is this table has a huge row number, near 1,800,000,000 rows !!!!

The only solution I find at the moment is to break it into few day and insert it into a temporary table, then remove duplicates from it.

This is my code : (ID is  Autoincrement)

INSERT INTO  temptable
(UserID , HitDate )
Select vs.UserID ,
        vs.HitDate
        From UserHitTbl vs where
 LEN(vs.UserID ) > 1 and
vs.HitDate  > '2013-06-12 14:32:59.783' and
vs.HitDate < '2013-07-12 14:32:59.783'
  DELETE
FROM temptable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM t6
GROUP BY UserID )

The above gives the following error:

  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup


texts: Setting up simple question & answer database

texts:
I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it doesn't have to be the final set up. We will not be storing any response data at this time, so a storage table is not necessary. The requirements are:

1) One question can have many possible answer options.
ex: (True, False) (Yes, No)

2) One question can only have one group of answers.
ex: Group One: (True, False) Group 2: (Yes, No)

3) Many questions can use one group of answers.
ex: Drivers License? Group Two (Yes, No); Married? Group Two (Yes, No)

I'm drawing a blank on how to build a group of answers off of a set of answers, and allowing a question to only pull from that group of answers. Any help would be greatly appreciated :)

Thank you in advance.


texts: How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time

texts:
I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory usage and long query times. When running locally on my machine, i'm seeing 6.5gb of memory used during the query, and the query takes around 8 seconds to fully run. However the weird part is that almost all of the time is spent in query planning, and not much in the actual execution:

Planning time: 7453.148 ms
Execution time: 159.609 ms

Interestingly enough, PREPARE-ing the query and then EXECUTE-ing it later causes the memory usage to spike during planning, but not during execution.

I have never seen a query take that long to plan, and none of our other large queries get anywhere near that.

My question is how can I diagnose or debug what the query planner is doing here? What tools does PostgreSQL provide to allow me to profile and diagnose why this query in particular is taking so much time and memory in the query planning stage, and what we can do to reduce the memory usage from the query planner.

Things i've tried:

Disabling GEQO entirely - had no measurable effect on the memory used, but did marginally increase the planning time to ~9 seconds up from ~7.5 seconds.
Tweaking GEQO threshold - had no measurable effect whether set to 2 or 99999 or anything in between.
Tweaking from_collapse_limit and join_collapse_limit - these had a small impact on the query, taking the planning down from ~7 seconds to ~5 seconds but not changing the memory usage at all (and the memory usage is really my main concern here)

I want to dig into this more, but without knowing how to profile what the query planner is doing, I'm basically shooting in the dark...

Edit: this is in PostgreSQL 9.6. We should be able to upgrade to 10 fairly easily, but 11 and up will need more research into if it's feasible.


texts: How to use a trigger to only insert one row

texts:
I am doing a third year project using an intel galileo that reads an RFID card, and signs a student into a class, and I am using multiple tables.

I have nearly completed it as all reads are been recorded into a table called read_log. What I want to achieve is when a record is entered into this read_log, set a trigger to just insert the card number, which again is been sent this table read_log.

So what i have so far is :

DROP TRIGGER IF EXISTS `test`;

CREATE DEFINER=`root`@`localhost` TRIGGER `test` AFTER UPDATE ON `readlog`
FOR EACH ROW
BEGIN
INSERT into attendence(S_no)

SELECT s.Stu_no from student s
JOIN readLog r ON s.Stu_no=r.Cardid WHERE s.Stu_no=r.Cardid;

END

I can confirm the trigger is working, as once i scan the rfid card over the galileo it inserts into the database, and the trigger triggers but enters the number 29 times, so there is 29 records entered everytime i read.

Can someone provide a tip as to what to look for or how to go about writing a trigger that will only insert one row. I have tried update instead of insert but this does nothing.


texts: Configure mongo for one shot full db read

texts:
I have a kubernetes cluster on bare metal with 3 machines having 8Gb of ram each. All my apps and a mongodb 4.0.9 replicaset runs on it.
There is an import program that:

Download a 8Gb mongo dump from external source and restore it in fresly created database A. There is one collection whitout indexes.
Fully browse the restored collection with one find({}) and 1M next().
Emit amqp messages for each doc, then messages are stored in mongo database B, C, D.
Drop the database A.

Part 2 use a lot of ram.

How should I configure mongo database A (and let database B normal) to reduce at max the footprint of the import operation? (Because more important tasks are running in the cluster)

For example, can I configure mongo to not create cache for the temporary collection?


texts:
I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL.

Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, now I've spent too much time trying to get that running on my winx64 machine, since MinGw won't compile it and activeperl modules hates me.

Are there any other worthy, free, tools to take a look at or should I get a Linuxbox up & running and try ora2pg that way?

Regards


texts:
I just started to learn MongoDB database and for my project.

I am trying to implement a MongoDB reference schema.

In my Menu table I would have a food ingredients attributes where it will store the food ingredients.

I am confused about designing my schema.

How am I going to build the schema with the food ingredients in Menu table?
Because it might be 100 ingredients, do I need to create an attribute for each of them ?
Or I can make one string attribute where I am going to store everything?

Here is an example of it.

var Menu = new Schema({
  foodTitle: String,
  foodIng: {
    title: String,
    ingredients: {
       rice: String,
       beans: String,
       meat: String,
       cheese: String,
       // etc...

    }
  },
});


texts: User has been denied select to table, but has access to table via synonym

texts:
User has been denied select to table T1 in DB1, but user can create synonym in DB2 and can select from DB1.TB1.
I can not deny create synonyms to user. Is it possible to prevent user to select after create synonyms for DB1.TB1?
use d2
create synonym dbo.cl for d1.dbo.cl
select top 100 * from dbo.cl
(100 rows affected)

select top 100 * from d1.dbo.cl
The SELECT permission was denied on the object 'cl', database 'd1', schema 'dbo'.

User has db_owner through AD group in d2


texts:
I've recently been doing a SQL course and I’m finding it incredibly easy to read.

If I can read SQL and write in it, could it be possible that it’s almost the same to MySQL and Mongol SQL? Just some clarification would be great.


texts: Permissions required for row counts to show up on Object Explorer Details

texts:
I'm having a weird security issue.  I have a user that's using SSMS 18.2 on a SQL 2016 server.  They are a member of db_datareader but when they pull up the the Row Count column in Object Explorer Details it's blank. As best I can tell it is requiring DBO in order to get the row counts to show up.

User with read access:

User with dbo access:

Is this a bug or intentional? Does anyone know if there is a lower level of permissions that will give this row count? I know there are plenty of other ways to get the row count, sys.partitions for example, however the user insists they want to use the OED window.


texts:
Given:
postgres=# \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 a      | uuid |           | not null |
Indexes:
    "b_a_idx" btree (a)

postgres=# \d c
                Table "public.c"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 a      | text |           | not null |
Indexes:
    "c_a_idx" btree (a)

postgres=# explain b;
ERROR:  syntax error at or near "b"
LINE 1: explain b;
                ^
postgres=# analyze b;
ANALYZE
postgres=# analyze c;
ANALYZE

Why does the following quering not use b.a's index?
postgres=# explain (select * from b join c on (b.a :: text) = c.a);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Merge Join  (cost=129.05..455.25 rows=12580 width=48)
   Merge Cond: (c.a = ((b.a)::text))
   ->  Index Only Scan using c_a_idx on c  (cost=0.15..64.55 rows=1360 width=32)
   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
         Sort Key: ((b.a)::text)
         ->  Seq Scan on b  (cost=0.00..28.50 rows=1850 width=16)
(6 rows)


texts: Creating a partition on my soft-delete indicator column

texts:
In SQL Server 2008 R2 I am exploring the use of partitioning tables.

We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL

I want to focus on the first case, the bit.

I am considering partitioning this table on the bit because the table is almost always queried and joined including this column.

How would I go about this process? Is it a bad idea?

I first tried to step through the wizard but the script it generated for me seemed awkward (multiple drops of constraints, creation of a new CI)


texts: How do I debug an "Operator used tempDB to spill" warning when statistics appear to be ok

texts:
I have a section of a complex query plan where a Sort operator appears directly after an Index Seek and SSMS shows a "Operator used tempDB to spill" warning.  It is reported to be taking 70% of the query cost.  The statistics appear to be ok going into and out of the operator.  How do I find out what is causing the spill and how to fix/improve it?
The branch of the query comes from a sub-query:
...
(
    select
        ev.ElectorID,
        t.DateTransferred as SentToGDS,
        row_number() over(partition by ev.ElectorID order by t.DateTransferred desc) as rowNum
    from
        ElectorVerification ev
    inner join
        Transfer t on ev.UploadTransferID = t.TransferID
    where
        ev.VerificationChannelID = 'ERTP'
) AS ...

Query Branch:

Sort Operator:

Output of Index Seek:


texts:
Trying to restore database, getting a dialog in SQL Server Management Studio

  You cannot select
  backup sets from different databases"

Any idea why I am getting this error?

BTW backup is from SQL Server 2000 and trying to restore in SQL Server 2000 using SQL Server 2008 R2 Management Studio


texts: How can I optimize my mysql setup to create my index faster?

texts:
I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package.  The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.

I have an MyISAM like this:

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

I inserted 800mil records (about 350gb data before inserting), then attempted to add the following index:

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

(Regarding the DISABLE KEYS command, I saw that suggested elsewhere to use BEFORE inserting data, and since the command never got to ENABLE KEYS, I assume it is serving no purpose for me.  I've mainly just included to be thorough in my description of what I'm doing.)

When I start the index job, the first status that shows in SHOW PROCESSLIST is "copying to tmp table".

After a few hours, I check bac, and the status remains on the "Repair With Keycache" message even after 24 hours.  I tried running the job on a slightly older server, and after 3 days, it still remains with status "Repair with keycache". Because of this, I've canceled the create-index command on this newer machine.

I've read that the "Repair With Keycache" can be very slow, and "Repair by sorting" is preferred in many cases.

Based on some Stack Exchange and random posts online, I added the following settings to my Mysql server:

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

I restarted the job, and the same process happened again (copy to tmp file, then repair via key cache).

After I killed the job, I noticed in mysql/error.log, there is a message, "myisam_sort_buffer_size is too small". This happened earlier in the day, and not exactly when I killed the job.

questions

Am I going about this wrong way?  I just want to be able to lookup my data quickly by some key (uniqname).
Is there any benefit to starting over w/ my table, adding the index from the beginning, use the DISABLE KEYS command, insert my 800mil records, and then ENABLE KEYS?  I read elsewhere that this can prevent the copying tmp table (which may only save me a few hours?)
Do I want this 'Repair by sorting'?


texts: How does the MySQL Query Optimizer react to a SELECT COUNT sentence?

texts:
Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at).

There's an index by ("country", "created_at").

If

SELECT COUNT(*)
FROM customers
WHERE ID > 10000
AND country = "US"
ORDER BY country, created_at

is executed, does the Query Optimizer select the same path as if no COUNT were requested, as in

SELECT * FROM ...//same conditions

?

Rephrasing, does MySQL Query Optimizer take into account sort orders when resolving SELECT COUNT?


texts:
I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions:

  Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

The above makes me worried that something else might update the sequence during my transaction.

So here is my current query:

BEGIN;
--[other queries and stuff here]
CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*) OVER ()) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;

Does SETVAL get called once for each row in my_temp_table or does it get called once?

Would it be better to have a separate query prior to creating my_temp_table to "reserve" the values before running? For example:

BEGIN;
--[other queries and stuff here]

CREATE TEMPORARY TABLE my_temp_sequence_value ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*)) AS seq_val
FROM
    some_other_table;

CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SELECT seq_val FROM my_temp_sequence_value) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;


texts:
I have a directory that will regularly have text files dumped in it that I'm trying to get imported into a single SQL table.
Basic format of the text files is as such:

[DATA]
Test: 12345
Test type: Random Test Information - Not Important
Test date: 01/10/2010
Test time: 12:00:00

Channel number: 1
Rate: 12.345 kN/min
Start load: 12.345 kN
Stop load: 12 %
Area: 12345.678 mm²

[RESULTS]
END TEST STOP
Maximum load: 12.345 kN
Maximum strength: 1.234 MPa

[GRAPHINFO]
Axis0=6;0;60;0;0;0
Axis1=3;0.000;25.000;255;0;65535
Graph1=0;1

[GRAPH]
Time [sec]  Load [kN]
0.050   0.123
0.100   0.456
0.150   0.789
0.200   1.123
0.250   1.456
0.300   1.789
0.350   2.123
0.400   2.456
0.450   2.789
0.500   3.123
0.550   3.456

[CHECKSUM]
123ABC4D

The 'Graph' section can vary in length so the solution would need to know when to stop.

The data I'm wanting to import is

Test
Test Date
Test Time
Load Rate
Start Load
Stop Load
Area
Maximum Load
Maximum Strength
The Graph data
Checksum

My first table looks like below:

----------------------------------------------------------------------------------------
|TestNo|TestDate|TestTime|LoadRate|StartLoad|StopLoad|Area|MaxLoad|MaxStrength|Checksum|

My second table looks like below:

------------------
|TestNo|Time|Load|

TestNo will be my primary key to keep the data in check.

Update
Now I've got the following

--Import Test Results File into temp TextImport table
DROP TABLE #TextImport
CREATE TABLE #TextImport ( line VARCHAR(8000) )

INSERT  INTO #TextImport
        ( line )
        EXECUTE MASTER..xp_cmdShell 'Type "\\FilePath\Example.txt"'

--Creates TestNo temp table to parse out data within
DROP TABLE #TestNo
CREATE TABLE #TestNo (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestNo into TestData temp table
INSERT INTO #TestNo(data, [row])
SELECT stuff(line, 1, 5+charindex('Test: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport

--Inserts TestNo from above into Results table
    --Assumes TestNo is always on Line 2
INSERT INTO [dbo].[Results] (TestNo)
SELECT
data
FROM
#TestNo
WHERE [ROW] = 2

--Creates TestDate temp table
DROP TABLE #TestDate
CREATE TABLE #TestDate (data VARCHAR(8000), [row] VARCHAR(8000))

--Inserts TestDate into TestData temp table
INSERT INTO #TestDate(data, [row])
SELECT stuff(line, 1, 10+charindex('Test date: ', line), ''),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ROW] FROM #TextImport

--Inserts TestDate from above into Results table
    --Assumes TestDate is always on Line 4
----Need to pull TestNo from above rather than hard-coding it
UPDATE [dbo].[Results]
SET TestDate = (
                SELECT
                data
                FROM
                #TestDate
                WHERE [ROW] = 4
                )
WHERE TestNo = 12345

So now the big questions are

Can I get the TestNo from elsewhere so I don't have to hardcode it?

I feel like I could pull it from #TestNo somehow??

How can I make this now loop through a folder instead of hardcoded and start pulling through files, processing them, then moving onto the next in the folder?
How am I going to deal with Graph data? Time/Load and splitting them apart and pulling them into their table?


texts: Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)'

texts:
I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below:

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30

select *
from @t as t
where code = (select min(code) from @t where id = t.id)

I want to see only the records with the code equal to a1, b1 and c4. Can over (partition by...) assign 1 to all those and 2 to b2 and c7 codes, etc, so that at the end I could say where row_number = 1 instead of using a sub-query?


texts: How to know who restarted and what caused SQL Server

texts: I need to know Who restarted SQL Server or What caused automatically to restart SQL Server, I have searched Event viewer for events 17163, 6005, 6006 , 6008 and 6009, but unsuccessful it had returned null,  i don't want information like last restart time where i can get it create_date from sys.databases of tempdb or start_time from sys.dm_os_sys_info.


texts: Microsoft SQL - How to merge when certain values match

texts:
Here is the database's structure:

^ Table:  Response_Master_Incident      ^
| ID                     | int          |
| Master_Incident_Number | varchar(20)  |
| Time_CallEnteredQueue  | datetime     |
| Time_CallClosed        | datetime     |
| Jurisdiction           | varchar(30)  |
| Response_Area          | varchar(30)  |
| Priority_Description   | varchar(30)  |
| Address                | varchar(400) |
| Apartment              | varchar(10)  |
| City                   | varchar(35)  |
| State                  | varchar(5)   |
| Postal_Code            | varchar(10)  |
| Call_Disposition       | varchar(30)  |
| Response_Date          | datetime     |
| Problem                | varchar(30)  |

^ Table:  Activity_Log                  ^
| ID                     | bigint       |
| Radio_Code             | varchar(20)  |
| Activity               | varchar(40)  |
| Date_Time              | datetime     |
| Master_Incident_ID     | int          |

^ Table: Case                           ^
| MasterIncidentID       | int          |
| CaseNumber             | varchar(20)  |

Here is my query currently:

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

SELECT Response_Master_Incident.Master_Incident_Number
  ,Response_Master_Incident.Problem
  ,CaseNumber.CaseNumber
  ,Response_Master_Incident.Time_CallEnteredQueue
  ,Response_Master_Incident.Time_CallClosed
  ,Response_Master_Incident.Jurisdiction
  ,Response_Master_Incident.Response_Area
  ,Response_Master_Incident.Priority_Description
  ,CONCAT (Response_Master_Incident.Address, ' ', Response_Master_Incident.Apartment, @NewLineChar, Response_Master_Incident.City, ', ', Response_Master_Incident.[State], '  ', Response_Master_Incident.Postal_Code) AS Full_Address
  ,Response_Master_Incident.Call_Disposition
  ,Activity_Log.Radio_Code
  ,Activity_Log.Activity
  ,Response_Master_Incident.Response_Date
  ,Activity_Log.Date_Time
  ,JurisdictionName
  ,CASE WHEN Activity_Log.Activity = 'Available' THEN 0
    WHEN Activity_Log.Activity = 'Terminated' THEN 0
    ELSE DATEDIFF(SECOND, (Activity_Log.Date_Time), (LEAD(Activity_Log.Date_Time, 1, 0) OVER (ORDER BY Response_Master_Incident.ID, Radio_Code, Activity_Log.ID))) END AS Seconds_In_Status
FROM
  Response_Master_Incident
  LEFT JOIN Activity_Log
    ON Response_Master_Incident.ID = Activity_Log.Master_Incident_ID
  LEFT JOIN Status
    ON Activity_Log.Activity = Status.Description
  LEFT JOIN CaseNumber
    ON Response_Master_Incident.ID = CaseNumber.MasterIncidentID

WHERE
  (Activity_Log.Radio_Name LIKE '[0-9][0-9][0-9][0-9]LM')
  AND Activity_Log.Radio_Code IS NOT NULL
  AND Response_Master_Incident.Response_Date BETWEEN '2016-09-01' and '2016-09-30'
  AND ((JurisdictionName = 'SomeJurisdiction') OR (JurisdictionName IS NULL))

ORDER BY Response_Master_Incident.ID,
Radio_Code,
Activity_Log.ID

So I'm querying another table using a key on the CaseNumber table, but I want the results returned in one row, concatenated.

For example, the following result:

^ Master_Incident_Number ^ CaseNumber ^ Radio_Code ^ Activity  ^ JurisdictionName ^
| 0000-000-000001        | 16-000001  | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000002  | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000001  | 1000PD     | Activity2 | SomeJurisdiction |
| 0000-000-000001        | 16-000002  | 1000PD     | Activity2 | SomeJurisdiction |

Would become:

^ Master_Incident_Number ^ CaseNumber            ^ Radio_Code ^ Activity  ^ JurisdictionName ^
| 0000-000-000001        | 16-000001, 16-000002 | 1000PD     | Activity1 | SomeJurisdiction |
| 0000-000-000001        | 16-000001, 16-000002 | 1000PD     | Activity2 | SomeJurisdiction |


texts: How expensive is a connection to pgbouncer?

texts: Is a connection to pgbouncer cheaper than a connection to PostgreSQL (assuming pgbouncer has already established a connection to PostgreSQL) ? How is the connection to pgbouncer made ? I found the documentation a bit unclear.


texts:
In one of my fact table which has close to 25 million records in it and now when a cognos team try to run the report on top of that it takes lot of time, So i am thinking of partitioning the table we store the records in fact table based on daily basis and we do have have the id calaendarday dma in the fact table as data type int.So is there a way i can partition this fact table based on  id calaendarday dma?

Please help me on the above query as it is impacting the cognos reports


texts: Database Open but Oracle gives ORA-01033: ORACLE initialization or shutdown in progress

texts:
We are using 4 node Oracle 10GR2 RAC in Windows x64. 3 instances of our database crashed today and later started normally. I started them by connecting (Remote Desktop) to individual instances and executing startup command in sqlplus.

I can connect to database using SYS account but when I try to connect using application user account, I get following error:

  An error was encountered performing the requested operation:

  ORA-01033: ORACLE initialization or shutdown in progress
  01033. 00000 -  "ORACLE initialization or shutdown in progress"
  *Cause:    An attempt was made to log on while Oracle is being started up
             or shutdown.
  *Action:   Wait a few minutes. Then retry the operation. Vendor code 1033

Interesting thing is my instances started and database are open according to following queries.

SELECT * FROM GV$INSTANCE;

SELECT * FROM GV$DATABASE;

I tried to use following command thinking that there is a mistake.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance

I also tried to control RAC node statuses using following command.

srvctl status database -d DBNAME

database status command is following:

Instance DBNAME1 is running on node NODE01
Instance DBNAME2 is running on node NODE02
Instance DBNAME3 is running on node NODE03
Instance DBNAME4 is running on node NODE04

So I can connect to database using SYS account, but WHY not application user account?

Situation resolved itself in 15 minutes. I can connect to database now. But there is nothing in the alert logs about this situation. Should I check additional places to learn about real problem? Is there anything I can do in similar situation?

After a while, I understood why this is happening! Oracle executes following command which we can see in alert logs.

ALTER DATABASE OPEN

Some time later , We can see following line in alert.log

Completed ALTER DATABASE OPEN

Only after this line, we can connecto to database. Interesting thing is in this 4 node RAC , we need to wait for this event in all instances before database start serving.