texts:
I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help.

I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and I have another table called tblOffer that has a column that is called InsertedID.

What I'm trying to do is if the two declared BuyerID from tblRecord are not the same as tbloffer in insertedID, then exclude that value.

I'm using SQL Server 2012 Management Studio.

SELECT
    COUNT(DISTINCT r.RecordID) AS Lines,
    COUNT(r.RecordID) AS Offers,
    MONTH(r1.Submitdate) AS M,
    DAY(r1.submitdate) AS D
FROM
    tblReq r1
INNER JOIN
    tblRecord r ON r1.ReqID = r.ReqID
INNER JOIN
    tblOffer o ON r.RecordID = o.RecordID
WHERE
    r.BuyerID IN (36, 160499)
    AND o.OfferTypeID IN (1, 3)
    AND r1.Submitted = 1
    AND r1.SubmitDate >= '2015-07-01'
GROUP BY
    MONTH(r1.Submitdate), DAY(r1.submitdate);


texts: Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

texts:
We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's generating pretty terrible execution plans on the new server, causing scans of a table that's around 6 GB.

It's a query with two InnoDB tables and a relatively simple join between them, on two columns that are indexed (PK in catalog_category_flat_store_1, but not a covering index on core_url_rewrite):

SELECT
    `main_table`.`entity_id`,
    main_table.`name`,
    main_table.`path`,
    `main_table`.`is_active`,
    `main_table`.`is_anchor`,
    `url_rewrite`.`request_path`
FROM `catalog_category_flat_store_1` AS `main_table`
    LEFT JOIN `core_url_rewrite` AS `url_rewrite`
        ON url_rewrite.category_id=main_table.entity_id
        AND url_rewrite.is_system=1
        AND url_rewrite.product_id IS NULL
        AND url_rewrite.store_id='1'
        AND url_rewrite.id_path LIKE 'category/%'
WHERE (main_table.is_active = '1') AND (main_table.include_in_menu = '1') AND (main_table.path like '1/2/5/204/225/%') AND (`level` <= 5) ORDER BY `main_table`.`position` ASC

catalog_category_flat_store_1 has 627 rows, and core_url_rewrite has 8,687,266 rows (kill me).

The EXPLAIN shows me that catalog_category_flat_store_1 is "Using index condition; Using where; Using filesort", and core_url_rewrite (the huge table) is "Range checked for each record (index map: 0xF4)", despite allegedly using the index on category_id as its key.

On the old server, catalog_category_flat_store_1 is "Using where; Using filesort", and core_url_rewrite doesn't have anything listed for "Extra", and appears to be using the correct index.

I noticed MariaDB has a lot of additional options for optimizer_switch, and I'm wondering if any of these options are causing the different optimizer behavior, so I figure it's worth testing. The problem is that I don't know what the values should be to make MariaDB act like MySQL.

Here's the old server, where the query runs just fine:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

And here's the new server, where the exact same query can end up running for ages:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

P.S. Greetings from Magento core_url_rewrite hell.


texts: MySQL 5.5 accepts password but then closes connection

texts:
Apologies in advance for lack of information. I have installed MySQL 5.5 x64 on a Windows XP Professional x64 development machine. Today, upon trying to connect with any tool(MySQL workbench, PHP script, PhpMyAdmin, or command-line utility), the password is accepted but the connection is immediately closed:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p
Enter password: ***************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.5-m8 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye

C:\Program Files\MySQL\MySQL Server 5.6\bin>

I have attempted removing all MySQL server-related files by deleting the installation directory and C:\Documents and Settings\All Users\MySQL\MySQL server 5.6, as well as D:\InnoDB Datafiles, where my InnoDB Tablespace lies. There is no important data, but I am unable to get MySQL server to operate properly. If any more info is needed, please do tell.

I have installed MySQL with the MSI installer and the instance config wizard. I have also tried the 5.6 development version, to no avail.


texts: Listing the Microsoft SQL Server configuration with the values set by mssql-conf?

texts:
The utility mssql-conf in /opt/mssql/bin/mssql-conf has the setting list which tells you possible keys,

$ sudo ./mssql-conf list
control.alternatewritethrough              Enable optimized write through flush for O_DSYNC requests
control.hestacksize                        Host extension stack size in KB
control.writethrough                       Use O_DSYNC for file flag write through requests
coredump.captureminiandfull                Capture both mini and full core dumps
coredump.coredumptype                      Core dump type to capture: mini, miniplus, filtered, full
filelocation.defaultbackupdir              Default directory for backup files
filelocation.defaultdatadir                Default directory for data files
filelocation.defaultdumpdir                Default directory for crash dump files
filelocation.defaultlogdir                 Default directory for log files
filelocation.errorlogfile                  Error log file location
filelocation.masterdatafile                Master database data file location
filelocation.masterlogfile                 Master database log file location
hadr.hadrenabled                           Allow SQL Server to use availability groups for high availability and disaster recovery
language.lcid                              Locale identifier for SQL Server to use (e.g. 1033 for US - English)
memory.memorylimitmb                       SQL Server memory limit (megabytes)
network.forceencryption                    Force encryption of incoming client connections
network.forcesecureldap                    Force using LDAPS to contact domain controller
network.ipaddress                          IP address for incoming connections
network.kerberoskeytabfile                 Kerberos keytab file location
network.tcpport                            TCP port for incoming connections
network.tlscert                            Path to certificate file for encrypting incoming client connections
network.tlsciphers                         TLS ciphers allowed for encrypted incoming client connections
network.tlskey                             Path to private key file for encrypting incoming client connections
network.tlsprotocols                       TLS protocol versions allowed for encrypted incoming client connections
sqlagent.databasemailprofile               SQL Agent Database Mail profile name
sqlagent.enabled                           Enable or disable SQLAgent
sqlagent.errorlogfile                      SQL Agent log file path
sqlagent.errorlogginglevel                 SQL Agent logging level bitmask - 1=Errors, 2=Warnings, 4=Info
telemetry.customerfeedback                 Telemetry status
telemetry.userrequestedlocalauditdirectory Directory for telemetry local audit cache

How do I get the values?


texts:
I'm looking for a way to extend the SHOW BINARY LOGS command to show the created date, or to at least show the time range each log is applicable for.

Is there a way to do this within MySQL or am I going to have to read each log individually to check what time period it covers?


texts: Migrating Large Database from SQL Server 2008 to 2014

texts:
I currently have on a large database (~500GB) on SQL Server 2008 that is going to be relocated to a new SQL Server 2014 server.

My aim is to keep migration time down to a minimum.

Migrating the database by backing it up to a network share and then restoring is going to be very time-consuming.

My plan is trying to find some way of keeping the databases in sync until the cut-over.

I’ve been looking into log shipping, but I’m not sure of the process completely (I’m not a DBA, I don’t even play one on TV), and since it doesn’t leave the database in an available state, I’m not sure that’s practical.

I’m open to suggestions, as well any tool recommendations that might assist.


texts:
I'm working with Firebird database. I have a big table with many thousand of rows. Each line records the status of an object at regular intervals for many objects. I need to register new properties for one object only.
I think I have 2 possibilities:

add the new columns to the table even if I have a lot of null values
create a table with the only properties that interest me and bind it appropriately to the first

which solution is the best for not having a loss of performance?


texts: Is there a cheap way to bulk select/insert data between SQL Server instances?

texts:
In my office, our employees use applications that are consistently writing transaction-type data to a number of databases on a SQL Server 2012.  The traffic consists mainly of inserts and updates but little if any deletes. We also have some other applications which provide reporting and monitoring services, polling these same databases. These databases are critical to the running of our business, and I'm worried about increasing impact that our monitoring and reporting applications might have on the operational side of things.

What I'm looking to do is setup a secondary DB server which would be used exclusively for reporting, which would require a duplicate of our live data but would only retain the most recent X hours of data, or even data between 1 and X hours old (i.e. no need for live data). No additional metadata would be required (i.e. transaction logs etc)

I've been reading up on the different types of replication, but am a bit confused, as I expect that the secondary DB would need to manually manage the purging of old records and am not sure how this would work with replication.  I've also investigated using backup/restore jobs, however since I would want to selectively choose which tables and rows to backup, I don't see how that could work.

Essentially all I believe I would need is a cheap way of selecting and inserting rows from one server to another in an automated fashion. Obviously, this could be accomplished using a few stored procedures and a scheduled script, however what I'd like to know is if SQL Server has another way of accomplishing this.


texts:
I'm trying to run a bash script to backup my database with Cronjob.

# Backup and compress each database
for database in $mysql_databases
do
  if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
        additional_mysqldump_params1="--single-transaction"
  else
        additional_mysqldump_params=""
  fi
  echo "Creating backup of \"${database}\" database"
  mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
  chmod 600 "${backup_dir}/${database}.gz" ${additional_mysqldump_params1}
done

but the --single-transaction option is not recognized


texts: Merge 2 columns and replace with specific output

texts:
The setup is a Debian 8 with Postgres 9.4 (64bit if that matters).

Given following table I want to "search" for a combination of columns first and column sub and replace their output with another string.

CREATE TABLE public.rawdata (
 first integer,
 sub character varying COLLATE pg_catalog."default",
 value integer
)

Example data:

INSERT INTO rawdata VALUES
  ('1','A','5994'),('1','B','28525'),('1','C','18577'),
  ('2','A','30522'),('2','B','5238'),('2','C','18268'),
  ('3','A','982'),('3','B','13401'),('3','C','24158'),
  ('4','A','8544'),('4','B','31575'),('4','C','16661'),
  ('5','A','600'),('5','B','5242'),('5','C','8740'),
  ('6','A','2557'),('6','B','69'),('6','C','31572'),
  ('7','A','4212'),('7','B','26269'),('7','C','27918'),
  ('8','A','29821'),('8','B','22603'),('8','C','32578'),
  ('9','A','8027'),('9','B','13668'),('9','C','32000'),
  ('10','A','17673'),('10','B','11723'),('10','C','8937');

For example, the two column '1' and 'A' should read in the output as 'cat'.

There should be no alteration of the source data.

I have read about using WITH for that but I am uncertain how to join the columns so that I can convert '(1, A)' to 'cat' and '(2, A)' to 'dog'.

I only need the output where I have a defined "replacement name".

The end-result should look like that:

Name Value
Cat 5994
Dog 30522

Sadly, I am limited to providing the output that way (if possible at all).
Normally, I would write something like:

select * from rawdata where first < 3

1   "A" 5994
1   "B" 28525
1   "C" 18577
2   "A" 30522
2   "B" 5238
2   "C" 18268

But the next step is something I can't figure out.


texts: Can we configure local and azure servers in a WSFC for Always On Availability Group?

texts:
Can we configure a local server and an azure server as a two node cluster for Always on Availability Group?
Thanks!


texts: MySQL user created via command line can't access PHPMyAdmin

texts:
I'm trying to create a read-only user with access to one database in PHPMyAdmin.  Since I'm a server newbie I followed some instructions line-by-line on setting up my database and PHPMyAdmin.  As such, my root account is not accessible via the GUI; only a full access child account.  This account cannot create users via the GUI, so I did the following via command line:

mysql -u root -p
mysql> create user 'readonly-user'@'localhost' IDENTIFIED BY 'password';
mysql> grant select on mydatabase.* to 'readonly-user'@'localhost';

If I try to log in via PHPMyAdmin as readonly-user, however, I'm not able to get in.

Is there something I need to do to register this account with PHPMyAdmin?


texts: Postgres count the same join by different values

texts:
I've been trying to create a query that counts the number of different types of events associated with each entry in another table

city                   job             job_events
id | name | state      id | city_id    id | job_id | type | info

Event type can be one of start, complete, failed, paused

A job can be paused multiple times, and can have a soft failure before a hard failure

I want to create a query that outputs the following:

city.id | number of jobs | num started jobs | num complete jobs | num failed jobs

I've tried

SELECT city.id, count(job.id) AS num_jobs, COUNT(started) AS started,
 COUNT(complete) AS complete, COUNT(failed) AS failed
FROM city
LEFT OUTER JOIN job ON city.id = job.city_id
LEFT OUTER JOIN job_events AS started ON type = 'started'
   AND started.job_id = job.id
LEFT OUTER JOIN job_events AS complete ON type = 'complete'
   AND complete.job_id = job.id
LEFT OUTER JOIN job_events AS failed ON type = 'failed'
   AND info = 'hard' AND failed.job_id = job.id
WHERE city.state = 'NSW'
GROUP BY city.id;

That gave me way more results than there were jobs, then I tried

SELECT city_id, count(job.id) AS num_jobs, COUNT(started) AS started,
 COUNT(complete) AS complete, COUNT(failed) AS failed
FROM
  (SELECT distinct (job.id) AS id, city.id AS "city_id"
   FROM job
   JOIN city ON city.id = job.city_id
   WHERE city.state = 'NSW') AS jobs
LEFT OUTER JOIN
 (SELECT distinct job_id FROM job_events WHERE type = 'started') AS start
 ON start.job_id = job.id
LEFT OUTER JOIN
 (SELECT distinct job_id FROM job_events WHERE type = 'COMPLETE') AS complete
 ON complete.job_id = job.id
LEFT OUTER JOIN
 (SELECT distinct job_id FROM job_events WHERE type = 'failed' AND info = 'hard')
  AS failed
  ON failed.job_id = job.id
GROUP BY city_id;

but likewise the results don't seem to match up.

Obviously, I'd appreciate a pointer on what to do to fix this, but also if I can get some help understanding what I've got conceptually wrong here in trying to build this query.


texts: Processes dissappear from processlist (MariaDB)

texts:
In my application I need to monitor list of process ids for some database user, so I execute SHOW PROCESSLIST command to get information.
The problem is that sometimes some Id dissapears from command output but it comes back on next execution. For me it seems like a bug, what cause this strange behaviour?

Example:

1934,1935,1936,1937,1938
1934,1935,1936,1937,1938
1934,1935,1936,1937,1938
1934,1935,1936,1938
1934,1935,1936,1937,1938

Version:

MariaDB [db_w1]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.1.24-MariaDB |
+-----------------+
1 row in set (0.00 sec)


texts:
I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof?

Details:

We have a huge table we don't control (maintained by a closed-source 3rd party product) that we have to do ad-hoc reporting against. The reports have to reflect (very nearly) up-to-date data throughout the day. The table is too big and too active for us to do the reports directly against it. It also, sadly, has no combination of column values that we can use to uniquely identify a row.

So the approach we've come up with is:

Use the trigger below to write inserts, updates, and deletes that occur on the big table (MainTable) to a temporary staging table (StagingTable).
Use a stored procedure we run separately, once a minute, to remove rows from the staging table, filter and massage the data, and use that data to update the table we actually run the reports against (ReportingTable). So the staging table typically has very little data in it; it might build up, oh, maybe as many as a thousand rows during any given minute.

So:

-(3rd party product)
    -> [MainTable]
        -(trigger)->
            [StagingTable]
                -(stored procedure)->
                    [ReportingTable]
                            ^
                            |
                   Reporting queries

The point is to do the filtering and massaging in our stored procedure and our transaction, not the 3rd party product's. We want to do as little as possible in the trigger because we don't want to disturb the 3rd party product's work. In particular it's vital that we don't introduce errors or delays that might make the 3rd party product's operation on MainTable fail.

Here's the trigger modified with descriptive names:

CREATE TRIGGER TheTrigger
ON MainTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.StagingTable
            SELECT RelevantColumns, 'D' AS RowType
            FROM   deleted
        INSERT INTO dbo.StagingTable
            SELECT RelevantColumns, 'I' AS RowType
            FROM   inserted
    END TRY
    BEGIN CATCH
        -- Do nothing
    END CATCH
END
GO

(After reading this question and answer I'm not sure that try/catch is helping us much...)

StagingTable has no indexes, no constraints, and no triggers; its only NOT NULL columns are also NOT NULL in MainTable (in fact, other than the RowType column, the column definitions are identical). And again, it normally doesn't have much data in it.

Our once-a-minute stored procedure tries to avoid keeping StagingTable busy for any longer than necessary:

-- Not using OUTPUT because at one point we had to support SQL Server 2000
-- (could probably use DELETE...OUTPUT now)
BEGIN TRAN
    INSERT INTO @inserts
        SELECT      *
        FROM        StagingTable
        WITH        (UPDLOCK, HOLDLOCK)
        WHERE       RowType = 'I'
    INSERT INTO @deletes
        SELECT      *
        FROM        StagingTable
        WITH        (UPDLOCK, HOLDLOCK)
        WHERE       RowType = 'D'
    DELETE FROM StagingTable
COMMIT TRAN

...where @inserts and @deletes are table-valued variables in the stored procedure.

Are there reasonable scenarios where this would disrupt the 3rd party product's inserts, updates, and deletes on MainTable? If so, is there anything further we can do to fix that? Either a tweak to the above, or an entirely different, better approach?


texts: E-A-V entity Attribute Value Generic Model with Min Max Values

texts:
I have an entity consisting of several attributes (can be added, can be removed from entity - and new attributes made as well). I need to be able to store the values - so for example I have a list of Attribute / Value pairs . When assigning Attribute to Machine - the Machine also has a min max value for the Attribute - how can I structure the tables to create the proper relationship. Consider the following tables

[Machine Table:  MachId],
[Machine_EAV Table:  MachId, EavId, ?MinVal?, ?MaxVal?,
?Value?],
[EAV Table: EavId, Name, TypeId] ,
[Type Table:  TypeId, TypeName]

In order to store values and have min and max Should I have a Table for the specific dataTypes like this: IntType_Attributes IntId, Machine_EAVId,  MinVal, MaxVal, Value

If anyone needs more info I can add that just ask and I will do my best to provide.
Note the Machine Parameters are constantly changing .. it needs to be flexible.

I am going to need to present a proper view as well - so I will need to be able create a view from the tables..


texts: At What Point Does Having an Index Become Efficient

texts:
I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large.  This creates a tradeoff, which is a design decision, but there should be an approximate table size before which using an index is absurd. (10 rows, for example, is probably way beneath that limit)

Does anybody know about where this limit would be, or know of a resource that would point me in the right direction?


texts: Table Design - Storing Mulitple Parameters Required for Data Warehouse Report

texts:
I am building a SQL Server Data Warehouse to store reporting data from a 3rd party system.

The reporting data is pulled from the 3rd party system by passing in a set of parameters (the parameters passed in could vary from minimum 3 to max 8. Depends on the report detail required by the user).

Here is my plans for the DW process:

SSIS package to run the report from the 3rd party system (using provided parameters)
Save the data to a Staging table
Use stored proc to take data from staging to the Dimension and Fact tables

My question is: Does the above process sound correct, and what is the best way to store the parameters required to run the report?

Should I store them in a SQL table which has just 2 columns (e.g. ReportSettingsV2 table) and the parameters are added as rows?
Or should the table have each parameter as a column, and if a parameter is not required for a particular load of the report then that will just be marked as NULL (e.g. ReportSettings table)?
Or a third option I am thinking would be to have the parameters stroed in an XML data type column?


texts:
One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the size of the database (100gb) so i cleared it. I ran a script to check the table sizes in this database and everything went back to normal. That is, no ridiculously fat table. However, the general size of the database didn't change despite the diet to the problematic table.

Any suggestions?

BTW, I am using SQL SERVER 2012

thanks, David


texts:
I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition.
There is no budget for enterprise nor any intention to not using the wild card searches, and there is no maintenance window.
Since wildcard searches are being made indexes are also of no use as it is doing a full table scan and thus creates locking.
I am using Ola Hallengren script for indexing but the indexes maintenance is taking 10-12 hours to complete for a 300gb database as it busy 24/7.Below is the script
EXECUTE dbo.IndexOptimize
@Databases = 'user_databases',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This has become a frustrating issue and I am about to quit. Please advise what are my options here.


texts:
I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles or privileges??

Query -

Select * from schema1.app_table1 ap1 inner join schema2.app_table2 ap2

The query would be run from oracle user app_user who has access to both the tables - app_table1 and app_table2.

It is possible to run the query without schema prefixes or synonyms just by modifying the user app_user.

Thanks for your help.


texts: How to use tsvector_update_trigger with jsonb column?

texts:
I need to create cached ts_vector column from jsonb value. I try to create a trigger
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', "translations#>>'{en,name}'"
  );

But in a result I get an error
ERROR:  column "translations#>>'{en,name}'" does not exist


texts: Keywords and reserved words: What's a poor boy to do?

texts: We have been using MySQL since its beginnings. Back then we thought that descriptive column names were a good idea. So we created columns like date, open, high, low, close, volume, signal and so on that reflected the terms we actually used in our work. Some of which were keywords or reserved words and some of which became reserved words, like signal. As the years rolled by many programs in a wide variety of languages were written to use those tables. I gather you can see where this is going. In the step up from 5.0 to 5.5 MySQL included signal as a reserved word, which effectively means that we can no longer upgrade as many programs access the column signal and recoding would be a HUGE effort. We'd really like to upgrade and, now that Oracle has its hands on MySQL, we would also like to consider a move to MariaDB. So, what's a poor boy to do? Any thoughts/ideas greatly appreciated.


texts: Get Job id OR Job name from within executing job

texts:
I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at that time, etc. The info is then exposed via SSRS so we can see an job failures / long running jobs / & jobs that haven't run but should have(or if someone messed with a schedule).

To do this, I have 2 job steps added into each job on every server and I would like to reduce the script down to just 1 step added to each job..possibly even call it from a network share..

But my issue is one of the 3 parameters I pass. I need to get the executing jobid or job name from within the executing job so I don't have to hardcode the name parameter.
The 3 parameters I pass are jobid, status(success/fail), errormsg. The powershell script I wrote is pretty straightforward.

Invoke-sqlcmd -ServerInstance "MYRemoteSYSTEM" -Database remoteDB -Query "exec dbo.JOB_LOG  'JOBNAME/ID','Success/FAIL','BAD MESSAGE HERE'"

This writes what I need to the table. I've looked at
msdb.dbo.sp_help_job /
msdb.dbo.sp_get_composite_job_info /
dbo.xp_sqlagent_enum_jobs /
but none of these will guarantee I get the ID or name of the correct executing job in the event that there are more than 1 jobs executing at the same time.

I've even tried looked at sys.sysprocesses but I think since the agent job is a powershell script, it shows as ".Net SqlClient Data Provider" so I can't trim the binary JOBID off the jobs that show as "SQLAgent - TSQL JobStep (Job 0xF1800243164745429C30474FFD5C990C : Step 1)"
---this I learned from Denny cherry's post - thanks denny-

Any ideas on how to grab the executing jobid would be greatly appreciated.

Thanks,

Chris


texts: Chain replication M1 (5.1) -> S1 (5.5) -> S2 (5.6) to limit downtime for an upgrade 5.1 to 5.6

texts:
I have to upgrade a production server from 5.1 to 5.6 version. The favourite option would be to dump the database from 5.1 and import on 5.6 but this operation has been estimated to 2 hours on my environment which will have an impact for customers (downtime during this maintenance).

I'm trying to find a solution to limit the downtime. A replication from 5.1 to 5.6 is not possible (only 1 major release between master and slave) so I wonder if the solution to setup a replication as M1 (5.1) -> S1 (5.5) -> S2 (5.6) is viable and has been already tested/validated by someone.

Thanks for your help.


texts: SQL Server execution plan using a view, but no view is inside the stored procedure

texts:
I am troubleshooting this slow stored procedure and when checking the actual execution plan I noticed a warning of a possible missing index. Taking a closer look, it's against one of the views, not an actual table. How can that be possible?

Of course, the physical table is being used but the actual view it is not!

The view is something like this:

CREATE VIEW [ws].[MyVIEWTable]
WITH SCHEMABINDING
AS
SELECT col1, col2
  FROM [dbo].[MyTable]

Why does the SQL Server engine use the view to retrieve data and not the actual physical table, which on this case would be dbo.MyTable?


texts: An Indexed View slows updates to a crawl. Is there a hint to work around it?

texts:
I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) and the self-joins were only in two tables that were moved to an outer view.

The problem was that the view alone would take 3+ seconds for compilation, which was much too slow for the use case. Following advice like this:

  Indexed view is also a great way to improve INNER JOINS performance.
  When two or more table are prejoined in an indexed view, the query
  optimizer can choose to retrieve the materialized view data instead of
  performing a costly join operation.

I was looking at solving the problem with an indexed view.

However, once this indexed view was created (it consists of about 20 joins) every update to the underlying table became very slow. Set Statistics IO On shows that it is re-accessing all of the underlying tables on statements that don't even modify any data as it reevaluates the view, for example an update that looks like this:

update a
  set y = b.y, z = b.z
from a inner join b on a.x = b.x
where exists (select a.x, a.y, a.z except select b.x, b.y, b.z)

So there are zero rows affected, but that whole indexed view and its dependent still gets a bunch of action and the execution time shows it.

Is there a way to get SQL server to not use that view unless it actually needs to update records? Perhaps there is a hint I don't know about.


texts: How to convert MySQL Keyword to normal column

texts:
We thought of upgrading MySQL to new version 5.5.16. While testing the scripts with the new version, I had found one column Maxvalue is a Keyword in latest version.

For this one, we need to change all the application where it is used.
Instead is there any way to make the keyword Maxvalue interpret as a Normal column, so that we will not have to change our application.

Maxvalue is just column name, it is not the maximum value.

And I am looking for some other option apart from backticks: `Maxvalue`.


texts:
I've recently started reading the book Designing Data-Intensive Applications.
This book has revealed that I have been using indexes incorrectly, in that I didn't realize that even if I have an index on two columns on a single table, my query can only use one of those indexes!
For the past 3 years, I've been using indexes as if they are composable, when they are very much not so! (This is a limitation of the B-Tree index - I won't go into the details).
One solution to this problem is to use a concatenated index, in which we take all the columns we want to search / filter by, combine the data into one piece of data, and then search on that combined piece of data.
This likely works great in practice, but again, the indexes are not composable. If I have a concatenated index (on my persons table) on the first name and last name column, and now I want to search / filter on age as well, I have to create a new index.
Are there any general solutions to this composability problem with indexes?


texts: MySQL innodb_row_lock_time is more than wall clock time

texts:
We recently started graphing the innodb_row_lock_time status variable in our monitoring system.

I noticed something I can't understand on a graph for a busy server:

Between 04:55PM and 04:57PM, the value of this counter changed from 488,101,000 to 488,504,000. The MySQL documentation says:

  innodb_row_lock_time
  The total time spent in acquiring row locks, in milliseconds.

How is it possible that during those 2 minutes, the server spent 403,000 ms in acquiring row locks? 2 minutes is only 120,000 ms

The only explanation that comes to mind is that this value is a sum of all threads but I cannot find any documentation to confirm this theory.

The server is MySQL 5.1


texts: Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

texts:
Problem

Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark.

The VM and db's are both fresh installs from vendor apt repos with default configs.

VM [Virtualbox 4.3.10]

1 cpu

ubuntu 14.04 [fresh install guest machine on virtualbox 4.3.10]

sysbench 0.4.12

Methodology

sysbench --test=oltp --oltp-table-size=50000 --mysql-db=test --mysql-table-engine=innodb --mysql-user=root prepare

sysbench --test=oltp --oltp-table-size=50000 --oltp-test-mode=complex --oltp-read-only=on --num-threads=1 --max-time=60 --max-requests=0 --mysql-db=test --mysql-user=root run

MariaDB 10.1.2 Result

OLTP test statistics:
    queries performed:
        read:                            1149876
        write:                           0
        other:                           164268
        total:                           1314144
    transactions:                        82134  (1368.89 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1149876 (19164.41 per sec.)
    other operations:                    164268 (2737.77 per sec.)

Test execution summary:
    total time:                          60.0006s
    total number of events:              82134
    total time taken by event execution: 59.7102
    per-request statistics:
         min:                                  0.22ms
         avg:                                  0.73ms
         max:                                170.77ms
         approx.  95 percentile:               1.24ms

Threads fairness:
    events (avg/stddev):           82134.0000/0.00
    execution time (avg/stddev):   59.7102/0.00

MySQL 5.6.22 Result

OLTP test statistics:
    queries performed:
        read:                            506296
        write:                           0
        other:                           72328
        total:                           578624
    transactions:                        36164  (602.73 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 506296 (8438.17 per sec.)
    other operations:                    72328  (1205.45 per sec.)

Test execution summary:
    total time:                          60.0007s
    total number of events:              36164
    total time taken by event execution: 59.7937
    per-request statistics:
         min:                                  1.04ms
         avg:                                  1.65ms
         max:                                 97.15ms
         approx.  95 percentile:               2.10ms

Threads fairness:
    events (avg/stddev):           36164.0000/0.00
    execution time (avg/stddev):   59.7937/0.00


texts: Load Balancer for MySQL MASTER MASTER Replication

texts:
We are having typical requirement.

We are having MySQL MASTER-MASTER Replication setup in place. We require to setup Load Balancer between these 2 MySQL servers.

I am aware of setting up WEB based Load Balancer, but we require load balancer for MySQL servers.

Requirement is there are one Application server and 2 DB servers, Application server will connect to Load Balancer, Load balancer will connect to DB servers by sharing the load.

Please let me know the best approach for this, also let me know if there are any pros and cons.


texts: How to automate CSV-exports of queries in PL/SQL Developer (Oracle)?

texts:
I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server).

I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer.

I need the ability to schedule an automatic (say, daily) export of a few tables (well, queries) in CSV format. Whether it's to my local computer, network, email --- anything.

Does anyone know how this might be achieved?


texts: Row based replication on slave where as statement based on Master

texts:
We have a master slave setup of MYSQL 5.6. Master and slave runs SBR replication where as slave is not writing binlogs.

We need to enable binlogs on the Slave server but we want the binlogs to be in RBR format on the slave instead. We want to ship them to Kafka and it wants RBR replication only.

Is this doable to have RBR on the slave where as it is getting data from master as SBR ?

Thanks


texts:
I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries?

Here is a row that is returned:

-[ RECORD 1 ]-------------------+-----
username                        | some name
some_column_name_1              | 2
some_column_name_2              |

Here is my crosstab query:

SELECT *
FROM crosstab( 'select username, stage, count from some_view order by 1,2')
     AS final_result(username TEXT,
         "some_column_name_1" BIGINT,
         "some_column_name_2" BIGINT,
);

I've tried little things like this with no luck:

"some_column_name_1" BIGINT 0
"some_column_name_1" BIGINT default 0
"some_column_name_1" BIGINT set default 0


texts: SQL Management Studio user permissions with multiple databases

texts: I'm trying to create SQL Management Studio user permissions for different users and different databases. I want to make sure that every user can only see one database and this user will also have to be able to backup and restore their databases. I have been playing around with the server roles and user mapping, but I cannot seem to find the right settings. With the sysdmin server role, a user is able to perform a restore, but then that user is also able to access other databases.


texts: When are UPDATE / INSERT operations processed in Online DDL in MySQL 5.6?

texts:
MySQL 5.6 includes functionality called Online DDL which allows ALTER operations to proceed on tables without applying a LOCK to the table (technically referred to as allowing Concurrent DML).

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

From what I can see, the mechanics behind this are that a copy of the table is made, on which the ALTER is made, and the data is copied between the source and destination tables. At the end of the process, the tables are renamed.

Any DML operations (UPDATE/INSERT) are cached during the ALTER process and then applied to the new table once the ALTER process is complete.

Is this correct?

For instance, if my ALTER takes 24 hours, is it the case that I won't see the results of DML operations for 24 hours, or is there some internal mechanism that cached DML operations are updated on the source table and then updated in the new table too?

I understand that the Percona pt-online-schema-change tool

https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

does something like this, but this tool can take up to 4 times as long to perform the table change as a native MySQL ALTER operation.


texts:
Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.

We would like to maintain only 3 months of data.

The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario?

Should we run a cron job to keep deleting data which is more than 3 months old? If so, how can we store it in a .sql file?


texts:
Is there a way to know the free disk space available on the drive that the back up file will be created?

For example, SQL Server is installed in Server 1 and the back up file is scheduled to be created in network \server2.

Is there a way to know the free space of \server2?

If I execute xp_fixeddrives, it will only give drive space where the current instance is.

While sys.dm_os_volume_stats will only give drives where database_files are located.


texts: How to partition an existing non-partitioned table

texts:
I have an existing table with data:

dbo.Test (col1,col2,col3....) ON [PRIMARY]

I need to change this table to be partitioned like this:

dbo.Test(col1,col2,col3....) ON Ps_Date(Col2)

How I can I achieve this without dropping and recreating the table?


texts: "Warnings: Operation caused residual I/O" versus key lookups

texts:
I've seen this warning in SQL Server 2017 execution plans:

  Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.

Here is a snippet from SQLSentry PlanExplorer:

In order to improve the code, I've added a non-clustered index, so SQL Server can get to the relevant rows. It works fine, but normally there would be too many (big) columns to include in the index. It looks like this:

If I only add the index, without include columns, it looks like this, if I force the use of the index:

Obviously, SQL Server thinks the key lookup is much more expensive than residual I/O. I have a test setup without much test data (yet), but when the code goes into production, it needs to work with much more data, so I'm fairly sure that some sort of NonClustered index is needed.

Are key lookups really that expensive, when you run on SSDs, that I have to create full-fat indexes (with a lot of include columns)?

Execution plan: https://www.brentozar.com/pastetheplan/?id=SJtiRte2X It is part of a long stored procedure. Look for IX_BatchNo_DeviceNo_CreatedUTC.


texts: What is causing Waiting for table level lock errors?

texts:
We got the database hanging twice already and trying to find a cause.

show processlist
Waiting for global read lock | INSERT INTO {myisam_table} ...

In here the disk space was full so we thought the problem was over after giving it some more but the next day at midday it hanged again:

show processlist
Waiting for table level lock | UPDATE {myisam_table} ...

What could be causing it?

Mysql Default Engine: InnoDB.

Database has a mixture of tables with both MyISAM and InnoDB engines.

Log posted here:

http://arturito.net/2013/08/28/mysql-waiting-for-table-level-lock-errors/


texts: How to get first value from list of value

texts:
I have data as follows

 Id   Col1   Col2
 ================
 101  101    abc
 101  102    def
 102  102    ght
 103  101    mgj
 103  102    dkf
 103  103    dfj

I need output as follows

 Id   Col1   Col2
 ================
 101  101    abc
 102  102    ght
 103  101    mgj

(by default, it should take  101 (col1), if not there 102(col1), or else 103(col1))

Any Idea?


texts: How to run Mongo database db.currentOp(true) command using API

texts:
Using the Mongo Java API I can run the currentOp() command like this:

MongoClient mongoClient = null;
mongoClient = new MongoClient( "127.0.0.1", 27017);
db = mongoClient.getDB("admin");
db.command("currentOp");

But I only get details of current operations. I need to get details of idle connections too.

With reference with this
https://docs.mongodb.com/v3.0/reference/method/db.currentOp/#currentop-examples

Behavior

If you pass in true to db.currentOp(), the method returns information on all operations, including operations on idle connections and system operations.

db.currentOp(true)
Passing in true is equivalent to passing in a query document of { '$all': true }.

If you pass a query document to db.currentOp(), the output returns information only for the current operations that match the query. You can query on the Output Fields. See Examples.

You can also specify { '$all': true } query document to return information on all in-progress operations, including operations on idle connections and system operations. If the query document includes '$all': true as well as other query conditions, only the '$all': true applies.

While using this command db.command("currentOp(true)");, I get an exception like this:

  "ok" : 0.0 , "errmsg" : "no such command: 'currentOp(true)', bad cmd: '{ currentOp(true): true }'" , "code" : 59}


texts: Azuredb The database 'tempdb' has reached its size quota

texts:
We are runnig a V12 Azure Database instance on the S3 tier. There is still about 100GB of a free space on the database. When loading 85MB XML file with SSIS running on a different non azure SQL server, and directly inserting it into the azure database, the insert crashes on the target database with the following error.

  The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible
  resolutions.

Is there any limit on the tempdb or any idea why this might be crashing? There is no way how the 85MB file can fill the remaining space on the database.  The tempdb seems somehow hidden, how can I monitor its usage?


texts: Session hanging with TM REQUEST and plan handle 0x0000

texts:
We have a periodic event where lots of connections hang on TM REQUEST (from sys.dm_os_waiting_tasks) and make no progress. KILL on the affected session_id does nothing

The server runs database mirroring (2008R2). Pausing and resuming mirroring fixes the issue when it happens.

The session stuck in this state are unkill'able. The load on the mirror is the usual, not bursty. Once a high number of connections are stuck in this state, the system freezes up and the mirror stops replicating (all queries get blocked).

Anyone seen this before and know what is causing it? There is nothing in the error logs.


texts:
We have a design in which a table stores the history of changes to an entity, and a view queries the table for the latest row for that entity.
Say, employee_history and employee (the view with latest status).
The view is defined using a rank() function:
create view employee as (
  select * from (
    select
      *
      , rank() over (partition by employee_id order by last_update_ts desc)
    from employee_history
  ) e
  where r = 1
)

There is an index over the employee_id and the update timestamp, both fields used in the view definition.
Most of the queries over this view are for a specific employee, with its ID. So the query engine makes good use of the index.
But now we need a query to find employees in some department, using the department name. This view definition cannot make use of the index.
How may I get a more performant query over the history table? I can create new indices and new views. I cannot create a new table containing only the current data; it has to be over the history table.
This is on Postgres 11.


texts:
According to resources, in Dimensional Warehousing, it is a good idea to place Dimension tables in a different filegroups from Fact Tables. In OLTP, is it a good idea to place Lookup tables in filegroups, from Transactional Tables. OLTP and Datawarehousing are different. (As Lookup tables are different from Dimensional Tables, Relational vs Kimball). Just wanted confirm this idea?

Microsoft FileGroups

SQL Saturday

•   "Put different tables used in the same join queries in different filegroups. This will improve performance, because of parallel disk I/O searching for joined data.

•   To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.

•   Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. This will improve performance, because of parallel I/O if the files are located on different physical disks."


texts: moving data from one db to another using SSIS

texts:
I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B.

table A contains many more fields than I need in table B, so the process needs SQL so I can specify fields to take. (I cant just lift the table from database A and drop it into database B).

If I were doing it in SQL I would just select field1, field2, field3 from table a and update table b with the results.

Can anyone offer me any pointers on how to do this in SSIS? I have had a good google around but can't find a definitive answer.

Many thanks


texts: Weird error on creating table and selecting rows from table

texts:
I have db and I am trying to create table called "sales_invoice_grid"

I am having bellow scenarios:

error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it already exists.

error 2: When select rows from "sales_invoice_grid" it gives the message that, the table is not exist


texts: Force chain to start immediately after completion instead of on a timer

texts: Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer?  I have only found ways to do a temporal restart, which doesn't really work for me as this is a data warehouse that has varying times of completion and cannot be set to just run every X minutes.


texts: Behavior of case sensitivity of RAISERROR when run remotely

texts:
I have a remote SQL Server that I can connect to using a local instance of SSMS.

When raising errors, we use

RAISERROR( 'This works', 16, -1 )

Remotely, this works fine if it's all uppercase, or even if any one letter is uppercase.

raiserRor( 'Still works', 16, -1 )

If I try to run it using all lowercase though, it does not work

raiserror( 'Error', 16, -1 )

It returns the error message

  Msg 121, Level 20, State 0, Line 3
  A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Even if I comment out the lowercase raiserror statement it still fails with the same error message.

If I log on to the server and execute these statements, they all work. It only seems to be when I connect remotely that this is an issue.

Why can't RAISERROR be completely lowercase? Even if commented?

The server is an install of SQL Server 2012 Developer edition on Windows Server 2012.

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I've used local installs of SQL Server Management Studio 2012 and 2014 and  tested against databases with 2005 and 2012 compatibility levels and the same behavior occurs.

I tested on other machines and the same results occurred. I don't currently have a machine to test outside of our companies network though.


texts:
I don't know yet what database I'll use in the future - MySql, Postgresql, Sqlite3 or anything else. At the moment I'm using a database N.

What's more or less universal approach for storing latitude & longitude? Varchar? Or big integer? Or float?

Should I store them in 2 columns or one?


texts: Keeping version history of functions in PostgreSQL

texts:
For many of my tables, I've added auditing triggers (based on this wiki page).  It works very well and has helped me several times figure out who changed what in the system.  We have a Python/Django application that sits on top of the data and that code is tracked in Git.  However, there seems to be one area of our system where changes are not tracked very well.  And that's the function/triggers, etc in PostgreSQL.  I wish there was a way I could add a similar audit capability to the schema as I have with the data itself.

How do DBA track these changes?  Note: I'm in a position where there is more than one person with sufficient privileges to "CREATE OR REPLACE" a function, so I can't necessarily count on a person to write a script and check it into Git and I certainly can't force them.  If possible, it needs to be automatic.

I've thought about writing a python script to "create" a script file and write it to a file and then programmatically commit changes to a git repo.

Another option would be to add some tables to my audit schema and just query each night and look for changes and right them to a table.  Certainly could work, but not quite as nice as being able to do a diff/blame via git.

This may not be the greatest question because I'm not exactly sure what I'm looking for and there may not be an exact "right" answer, but I would like to know what people do.  I've done some "googling" on the topic, but I'm not finding a lot (maybe I'm just using the wrong terms).

Finally, I'm currently using PostgreSQL 9.1.


texts: Unexplainable behavior of SQL Server under Windows Authentication

texts:
The versions affected are SQL Server 2005, 2008, 2008 R2.

If we grant CONNECT SQL to public server role, any AD account can log in to server.
So we'll have all the AD members on the server without any corresponding login.

This is not documented anywhere, and it's not reproducible on SQL Server 2000 or starting with SQL Server 2012, i.e. if we do the same on 2012, any AD account trying to log in will receive the 18456 error

  Login failed for user ...

with the reason

  Could not find a login matching the name provided

That's fine and that is expected, but for what reason 2005 - 2008 R2 behave differently?

UPDATE
I want also to ask the community about the behavior that is still presented at least on SQL Server 2012:
Let's have an AD account that is not mapped to the server, so it cannot access the server because there is no login for it, niether for any Windows Group he is member of. Now I execute the code:

    exceute as login = 'myDom\myAcc';

    select *
    from sys.fn_my_permissions(null, 'server')

The first interesting thing is that I CAN impersonate the login that does not exists. The second thing is this non existing login HAS a server-level permission VIEW ANY DATABASE.

When I was on SQL Server 2008 R2 I thought it's normal, only undocumented, because ALL THE AD PRINCIPALS ARE MEMBERS OF PUBLIC (so it was sufficient to grant CONNECT SQL to public and all the AD members could access the server)

On SQL Server 2012 granting CONNECT SQL to public does not allow to the whole AD to log in, so I thought it was a bug fixed.

But how then can I impersonate smth that does not exist and from what this non existing login inherits a permission?

Or maybe I can ask this way:

It's clear that non existing login inherits his VIEW ANY DATABASE permission from public server role, but why then he does not inherit CONNECT SQL if I grant it to public? Is it consistent behavior? Was it more consistent prior to 2012? Or maybe it was just a bug introduced with SQL Server 2005 and fixed only in the half?


texts:
I have  this SQL code

SELECT *,(SELECT COUNT(e.clients_idclients) FROM experienced e JOIN designation d WHERE e.designation_iddesignation = d.iddesignation AND e.clients_idclients = clients.idclients AND d.iddesignation IN('268'))as de_count
FROM `clients`
LEFT JOIN `experienced` ON `experienced`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `designation` ON `designation`.`iddesignation` = `experienced`.`designation_iddesignation`
LEFT JOIN `industry` ON `industry`.`idindustry` = `experienced`.`industry_idindustry`
LEFT JOIN `company` ON `company`.`idcompany` = `experienced`.`company_idcompany`
LEFT JOIN `academic_qualifications` ON `academic_qualifications`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `qulification` ON `qulification`.`idqulification` = `academic_qualifications`.`qulification_idqulification`
LEFT JOIN `institute` ON `institute`.`idinstitute` = `academic_qualifications`.`institute_idinstitute`
LEFT JOIN `job_category` ON `experienced`.`job_category_idjobCategory` = `job_category`.`idjobCategory`
LEFT JOIN `client_tag` ON `client_tag`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `cv` ON `cv`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `tag` ON `tag`.`idtag` = `client_tag`.`tag_idtag`
WHERE `clients`.`status_idstatus` != '10'
AND `clients`.`clientsAge` >= '0'
AND `clients`.`clientsAge` <= '100'
AND `clients`.`clientsOtherQualifications` = '1'
AND (`clients`.`fullName` LIKE '%custorme%'  OR `clients`.`clientsEmail` LIKE '%custorme%' OR `designation`.`designationName` LIKE '%custorme%' OR `cv`.`cvName` LIKE '%custorme%' OR `industry`.`industryName` LIKE '%custorme%' OR `company`.`companyName` LIKE '%custorme%' OR `qulification`.`qulificationName` LIKE '%custorme%' OR `institute`.`instituteName` LIKE '%custorme%' OR `job_category`.`jobCategoryName` LIKE '%custorme%' OR `tag`.`tagName` LIKE '%custorme%')
GROUP BY `clients`.`idclients`
HAVING de_count > 0
ORDER BY `clients`.`idclients` DESC
LIMIT 30

Can I write this  SQL code as

HAVING > (SELECT COUNT(e.clients_idclients) FROM experienced e JOIN designation d WHERE e.designation_iddesignation = d.iddesignation AND e.clients_idclients = clients.idclients AND d.iddesignation IN('268'))

without defining it next to the top select?


texts: Wikipedia says that information schema is an ANSI-standard and I found many implementations of it but I can't find a paper by ANSI that describes it. Can you help me?


texts:
Does Cassandra support I18n(Internationalisation)? if yes then can you please guide me how? i want to create table in any other language as shown below and I am getting syntax error:
How to enable I18n format in Cassandra?

cassandra@cqlsh:test> CREATE TABLE कंप्यूटर( Invalid syntax at line 1, char 14 CREATE TABLE कंप्यूटर( ^


texts: How implement a cancel attribute in a table?

texts:
Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled.

For canceled data I want to store the additional information

by whom
when
why

In about 95 % of the queries I only want to select the non canceled rows.

Further I want to be able implement unique constraints to some columns for the not canceled columns.

What are the pros and cons of using NULL in some column as indicator for not canceled ?


texts: Shrink Database Did Not Reclaim Much Space

texts:
I have a SQL Server (2005 Developer Edition) database that has grown too large for it's drive.

Yesterday evening I dropped a large table that was no longer needed and last night I set up a maintenance plan to shrink the database with 25% free space to remain after shrink, and to rebuild the indexes.

The job ran successfully, however the size of the .mdf file was only reduced by about 1.5GB even though the sp_spaceused results indicate that most of the database is unallocated space.

database_name,database_size,unallocated space
redacted,28717.69 MB,28349.03 MB

I was expecting a far larger decrease in the size of the database.

Did I improperly configure the maintenance plan? Am I misunderstanding what happens when a database is shrunk? Both?


texts: Retrieving row count without using Count function

texts:
I wrote a query below which shoots me a syntax error why would it do so,

SELECT MAX('Row') FROM

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)

Error Desc:

Incorrect syntax near ')'.

I don't get it :(


texts:
Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is

SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, c.ccStartingDate, c.ccCreatedAt, l.uId, l.ccId
FROM followingrelations f, conferencecreate c, likesbyuser l
WHERE f.followerId = 'login_user_id'
      AND c.uId = f.leaderId
      AND c.ccFlag = 1
      AND c.ccStartingDate >= 'current_time_and_date_now'
      OR l.uId = f.leaderId
GROUP BY c.ccTitle
ORDER BY c.ccId DESC;

Expectation:

Want to fetch data where uId and leaderId are matched from followingrelations and conferencecreate tables respectively.
Beside above condition, want to fetch data also if any user likes any post. The condition I putted like this way OR l.uId = f.leaderId

Am I on right track?

I implemented this query but nothing displaying. Any idea how to solve this issue plz?

My database design is in following:


texts: Help me choose a RAID level combination for a SQL Server 2008 instance

texts:
I am going to rebuild one IBM 3400 server from scratch.  This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.

I am going to make new RAID configuration.  I have 6 SCSI 73 GB drives inside the machine and an IBM ServerRAID 8K controler.  What would be a good way to set the RAID levels? Should I have two, three or one field on my controler?

I am considering to make one of following solutions:

Use all the disk and make a RAID 10 pool.
Use 4 disks for a RAID 1e pool and use it to store the database data and OS, and use the other 2 disks in a RAID 0 pool and use that to store the database logs.
Some other combination.

Is a larger stripe unit size better?

This server will be a subscriber to a replicated database.  Its primary task is going to be reporting and data retrieval, with only the replication agent making writes. The size of the database is around 90 GB.


texts: How to create multi volume file backup using symantec backup.exec

texts: I am using symantec backup .exec for backup and restore in mysql server . Now I am  in need to take  larger database backup  in tape .But it exeeds the tape storage and I am trying to create a multi volume file ie, a single backup in multiple tapes. Please do help me to sort out this .


texts:
I am running mongo sharded cluster with two replica set as shards.
One set is working fine but replica secondary on other replica set is not working. When ever i restart it it gives me this error and failed.

2014-09-04T08:16:16.634+0530 [rsSync] replSet initialSyncOplogApplication applied 1001 operations, synced to Sep  3 15:26:47:3d
2014-09-04T08:16:17.164+0530 [clientcursormon] mem (MB) res:12725 virt:2814759
2014-09-04T08:16:17.164+0530 [clientcursormon]  mapped (incl journal view):2813248
2014-09-04T08:16:17.164+0530 [clientcursormon]  connections:16
2014-09-04T08:16:17.164+0530 [clientcursormon]  replication threads:32
2014-09-04T08:16:21.454+0530 [repl writer worker 1] ERROR: exception:  :: caused by :: 56 An empty update path is not valid. on: { ts: Timestamp 1409738211000|67, h: -8833753959820233956, v: 2, op: "u", ns: "ibeat20140903.visitor", o2: { _id: ObjectId('54060caebc88dbf98cc57898') }, o: { $set: { : 20, AID-41575656: 17, count: 1938, pgAccessTime: 1409738211851 } } }
2014-09-04T08:16:21.454+0530 [repl writer worker 1] Fatal Assertion 16361
2014-09-04T08:16:21.454+0530 [repl writer worker 1]

***aborting after fassert() failure

I tried clearing the db folder and start replica to resync from start but still didn't work.


texts: Is possible to start a postgresql streaming replication using pg_dump instead of copying the database?

texts:
Hi i am able to start streaming replication with postgresql, copying the database files with doing theis command:

select pg_start_backup('clone', true);

That's right, but the thing is that i need to copy all the database files in /var/lib/postgresql/data to one server to another. That files are really big because of indexes for example.

I want to know if it is possible to do the same using a simple dump and restoring it on the slave, what is smaller and faster to transfer in the network, it will work? And what should i do to work?


texts:
In my SQL environments most of the SQL server are running under '.\Local User' with Power user groups rights and some sql servers are running under virtual accounts 'NT Service\MSSQLSERVER'.

I know its best to run SQL service  under Domain accounts, but I need to know best compared  to Local User '.\Local User' vs Vertual account 'NT Service\MSSQLSERVER'.


texts: MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group

texts:
A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit,  14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary.
A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:
Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:
ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change.

What impact does this change have?
Is the cluster still active?
Is the data still replicated to the secondary server?
Does it have a positive or negative impact on the performance?
Has a safety feature just been disabled?


texts: mysqldump with automatically generated export name

texts:
When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD).

Now, I want to do this in a batch file since I want to make weekly dumps automatically by simply running a simple script (our DB is running on a windows machine)

Do I have to make the filename myself or is there a way to have mysqldump do it for me? I can use date /t and parse it but it would be nice to know if there was some built-in functionality.


texts: SQL Server 2008R2 SP1 instance not available for adding features

texts: I need to add fulltext capabilities to an existing SQL Server 2008R2 instance but the one I need to upgrade is not actually available. The combobox shows only the one selected, as in the picture. It looks like it could be related to different versions of the two instances but I can't explain why. Any ideas?


texts:
This is my first question, hope I do it right!

I am working on creating a process that will sync server objects between our AG nodes. We are using SQL Server 2016 with 4 nodes. Two nodes in one data center and two nodes in a different data center.  I am currently using PowerShell to copy my logins, jobs, SSIS catalog, operators, and linked servers. I want to automate deploying changes made to these objects across all four nodes. Is there a tool made for this already?


texts:
I want set up replication between an Amazon RDS MySQL DB instance and a MySQL  instance that is external to Amazon RDS.

but  Slave_IO_Running: Connecting
how to solve this problem?

I have already done the following commands.

mysql>CALL mysql.rds_set_external_master ('192.168.xx.xxx', 3306,
    'repl2', '111111', 'mysql-bin-changelog.000003', 598, 0);

mysql> CALL mysql.rds_start_replication;

+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.01 sec)


texts: SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell

texts:
I have a SQL agent job set to execute a SQL query against a single database and then save each row into a different file with powershell before zipping the files up and emailing me.

In one of my SQL instances this job runs just fine without any modification. In another instance the execution of the query times out after 5 seconds. I've looked and I can't find any timeout settings on the server or in the script that are remotely that low.

I tried modifying the connection string and sqlcommand both to have timeout values of 600 and for some reason this is still timing out after an extremely short period of time. I've tried running the query via SSMS and the results are returned in about 15 seconds.

What else can I look into to try and troubleshoot this?


texts:
I have a table product and its columns are maker, type and model. I want to get the makers who produce 6 product types and more than one model. Here is something I am trying:

select maker, type
from product
group by maker
having count(distinct type) = 6
   and count(distinct model) > 1 ;

It gives error.

If I say I need 1 type and query is:

select maker, min(type)
from product
group by maker
having count(distinct type) = 1
   and count(distinct model) > 1 ;

then it works. Why?


texts: Rebuilding large columnstore indexed tables - am I doing it right?

texts:
We are planning to move the database from SQL 2014 ENT into 2016 ENT.
We realized the data currently in columnstore table are not aligned in time when they are loaded, so we need to re-align the data.

Stats:

3 big tables (columnstore indexed)
the big tables has 60+ billion rows
4 data files  (round robin)
partitioned by monthly
SQL Server 2014 ENT, 128 GB RAM
High Performance VM platform, 32 CPUs

File groups/datafiles:

PRIMARY
DATA (4 data files, each data file on 2TB disk)
LOG

Parition details:

Monthly partition function
All paritiona scheme on DATA FG

Table sizes (#rows):

T1   34,807,580,311
T2   16,458,306,369
T3   10,170,792,290

What I plan to do:

drop the columnstore index, create row-store index (this is to align the data in logtime)
then drop the row store index, convert the table into column store index (better compression and queries later)

I am concern this might took extremely long time to rebuild. Am I taking the right approach?

DDL:

CREATE TABLE [dbo].[T1](
[Id] [int] NOT NULL,
[C1] [smallint] NOT NULL,
[C2] [int] NOT NULL,
[C3] [int] NOT NULL,
[C4] [int] NOT NULL,
[C5] [datetime2](0) NOT NULL,
[C6] [real] NULL,
[C7] [real] NULL,
[C8] [real] NULL,
[C9] [real] NULL,
[C10] [real] NULL,
[C11] [datetime2](3) NULL,
[C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T1_ColumnStoreIndex] ON [T1]

CREATE TABLE [dbo].[T2](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [int] NULL,
    [C9] [int] NULL,
    [C10] [tinyint] NULL,
    [C11] [tinyint] NULL,
    [C12] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T2_ColumnStoreIndex] ON [T2]

CREATE TABLE [dbo].[T3](
    [C1] [int] NOT NULL,
    [C2] [smallint] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [int] NOT NULL,
    [C5] [int] NOT NULL,
    [C6] [datetime2](0) NOT NULL,
    [C7] [real] NULL,
    [C8] [real] NULL,
    [C9] [real] NULL,
    [C10] [real] NULL,
    [C11] [real] NULL,
    [C12] [real] NULL,
    [C13] [real] NULL,
    [C14] [varchar](50) NULL,
    [C15] [datetime] NULL,
    [C16] [int] NULL,
    [C17] [int] NULL,
    [C18] [float] NULL,
    [C19] [tinyint] NULL,
    [C20] [tinyint] NULL,
    [C21] [datetime2](3) NULL,
    [C22] [tinyint] NULL
) ON [DATA]

CREATE CLUSTERED COLUMNSTORE INDEX [T3_ColumnStoreIndex] ON [T3]

This may not be Q&A but please I believe I can gain valuable inputs here.


texts: Using @@PROCID to return name of stored procedure

texts:
We are migrating from Sybase to MSSQL 2016, and have triggers on some Sybase tables which we have ported from SYB to MSSQL. These triggers are really useful in SYB because they tell us the proc wrote the change to those tables so we know where to look when we have any issues.

Unfortunately, (object_name(@@procid)) does not work the same in MSSQL as it does in SYB (In MSSQL it is giving us the trigger name which is not very useful…), so we need to find a solution to this issue. Any help?


texts: How do I recreate the XE database in Oracle 11g Express?

texts:
My Oracle XE database was  useless mess of unused tables etc. Using SqlPlus and several google results, I finally managed to drop the XE database, but now when I connect with SqlPLus IO cannot create a database. For any CREATE DATABASE command issue, I get back the error ORACLE not available.

I assume this is as I deleted the only db on that server, which only has room for one db, SqlPlus now gets confused as to how to connect, still trying to find XE. How can I connect as admin without connection to a db. so that I am connected to Oracle but not to a db? This seems to new the most likely solution.


texts: SSMS Results to Separate tab ignored on SELECT TOP 1000 Rows Right Click Menu

texts:
I have configured SSMS to display results to a separate pane which works fine when I create new queries using the "New Query" button.

However, when I right click on a table and click SELECT TOP 1000 Rows it displays the result in the "old style" window with code at the top and results at the bottom.

Is there something I have missed or is this a feature?

I am using Management Studio 17.9.1


texts: MongoDB stops responding after 2 days of working

texts:
I've written small application in Python (I used Twisted). Program receives data and writes it to MongoDB. Every 2 days MongoDB stops responding:

2015-02-11T10:47:20.918+0100 [initandlisten] can't create new thread, closing connection

I don't know what is going on because we have ~10 concurrent connections which is very small amount. Just before it crashed mongostat had return that output:

insert  query update delete getmore command flushes mapped  vsize    res        locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn repl       time
    10     *0      5     *0       0    11|0       0  5.03g  17.7g  2.22g           .:0.3%          0       0|0     0|0    10k     4k    11  PRI   10:43:55
    12      2      6     *0       0    14|0       0  5.03g  17.7g  2.22g           .:2.1%          0       0|0     0|0    12k   232k    11  PRI   10:43:56
    14      1      7     *0       0    16|0       0  5.03g  17.2g  2.21g  foo:0.9%          0       0|0     0|0    15k   230k    11  PRI   10:43:57
    10      1      5     *0       0    11|0       0  5.03g  17.7g  2.22g           .:2.0%          0       0|0     0|0     9k     4k    11  PRI   10:43:58
    12     *0      6     *0       0    13|0       0  5.03g  17.7g  2.22g  foo:0.6%          0       0|0     0|0    13k     4k    11  PRI   10:43:59
    14      1      7     *0       0    15|0       0  5.03g  17.7g  2.22g           .:2.3%          0       0|0     0|0    14k     5k    11  PRI   10:44:00

Load average: 0.49,  0.42,  0.32

Free RAM: 12 GB

OS: FreeBSD

Free disk space: 76 GB

Application looks like this:

class Device(protocol.Protocol):

    def __init__(self, factory):
        self.db = factory.db

    def dataReceived(self, data):
        deferred = threads.deferToThread(self.handle, data)
        deferred.addCallback(self.on_success)
        deferred.addErrback(self.on_error)

    def on_success(self, response):
        if response is not None:
            self.transport.write(response)

    def on_error(self, failure):
        logging.error('Error in deferred: %s' % failure.getErrorMessage())

    def handle(self, tracker, input):
        self.db.buffer.insert({ ''' writing data ''' })

class Rpc(jsonrpc.JSONRPC):

    def jsonrpc_get_by_id(self, id, limit=1000):

        deferred = threads.deferToThread(self.get_by_id, id, limit)
        deferred.addCallback(lambda result: json.JSONDecoder().decode(json.dumps(result, default=json_util.default)))
        deferred.addErrback(lambda failure: logging.error(str(failure), exc_info=True))

        return deferred

    def get_by_id(self, id, limit=1000):

        client = pymongo.MongoClient()
        result = []

        db = client.foo

        for doc in db.record.find({'_id': {'$gt': ObjectId(str(id))}}).limit(limit):
            result.append(doc)

        return result

class Server(protocol.Factory):

    def __init__(self):
        self.client = pymongo.MongoClient()
        self.db = self.client.foo

    def buildProtocol(self, addr):
        return Device(self)

reactor.listenTCP(6969, Server())
reactor.listenTCP(6970, server.Site(Rpc())) # JSON RPC
reactor.run()

I realized that this situation occurs when db.serverStatus().connections.totalCreated reaches high number like 300k.

Any idea what is the reason of that behavior?


texts:
I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would it just block all reads to just that specific table until that query finishes executing? I assume for sure that not all tables would get blocked but I am concerned if the table being ALTER-ed itself is going to get blocked.

I did some research on my question and am referring to the online ALTER TABLE operation at http://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

I have heard from others that the slave does not block reads in this case if it were running 5.1 MySQL, but does it block for 5.6?

And yes, all the tables in the DB are InnoDB

Thanks!


texts:
My application is running on Oracle Database which hosts 3 database instances. All of sudden one of my database instance is not running. when connected through sysdba it says "connected to idle instance". Here are the activities performed

Oracle version:Release 11.2.0.4.0

sudo su - oracle
export ORACLE_SID=testdev1
sqlplus / AS SYSDBA

  Connected to an idle instance.

shutdown immediate

  ORA-01034: ORACLE not available

  ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2:
  No such file or directory

startup

  ORA-01078: failure in processing system parameters

  ORA-01565: error in identifying file
  '+DATADG/testdev1/spfiltestdev1.ora'

  ORA-17503: ksfdopn:2 Failed to open file
  +DATADG/testdev1/spfiletestdev1.ora

  ORA-29701: unable to connect to Cluster Synchronization Service

can anyone help me what is the issue ?


texts: PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

texts:
I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS.

For some tables, when I try to visualise in Squirrel SQL, I get the following:

Squirrel "Row count" tab displays 351
Squirrel "Content" does not show anything
but executing the query "select * from myTable" returns all the lines

For the same table, when ruby tries to extract one record, it does not find it. But I can check that the record exists in Squirrel.

All indexes were rebuilt, no transaction pending.
The same data base is accessed with Ruby and Squirrel, with the same user.

What could be the reason, how to solve this blocking issue ?

Thanks,
Fred


texts: Redesigning a multi-instance MySQL server for performance and resiliency

texts:
I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box.

Is it possible to have an active-active MySQL cluster, with each individual node in the cluster hosting a single instance, but with the capability of having one or more of the instances failing over to another node if the primary hosting node fails?

Example

Current layout

Connection - Server1:{instance port}

Server - Server1

Instance - Instance 1; Instance 2; Instance N

Drive -    LocalDiskA; LocalDiskB; LocalDiskC

Proposed layout

Connection - Connection:{instance port}

Server -   Node1;        Node2;         NodeN

Instance - Instance 1;   Instance 2;    Instance N

Drive -    SanDrive A;   SanDrive B;    SanDrive C

Failover capability

Connection - Connection:{instance port}

Server -   Node1;                    Node2;         XNodeNX (offline)

Instance - Instance 1, Instance N;   Instance 2;

Drive -    SanDrive A, SanDrive C;   SanDrive B;

I've read a few articles on MySQL clustering, and most of what I've seen just refers to being able to load balance a single instance across multiple servers, which unfortunately doesn't meet my requirements (I need to keep identically structured databases separate, as each instance is environment specific).

I know this is possible with SQL Server, but don't know enough about MySQL so any help you can give would be greatly appreciated.


texts: Stored Proc Logical Reads Spiking Daily At The Same Time

texts:
I have a procedure that I optimized in a dev environment.  It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds.  I got it down to 96k reads and 8 seconds.  It was pushed to our production database.  Then we noticed that for the last 3 days from 11:30AM-2PM the performance for this proc was tanking with the run time going anywhere from 40 seconds to 2 minutes and the logical read count jumping to 12 million.

This same query on this same server and DB goes back to 70-95k reads and a couple of seconds around 3PM.

This issue CANNOT be reproduced in the dev or QA environments and the schema structure is the same between them all and prod.

At first I thought sniffing or a bad execution plan but that doesnt explain the consistent timing aspect of it all.

I cant tell if I am over looking something that is staring me in the face or if there is more to the story.  What could cause this read spike?  Any feedback is welcome.


texts: mysql - need explain for group by behavior

texts:
Can someone to explain below group by result

I have a simple table "TableA" like this

ID --- Name
(1  --- A_1)
(2  --- A_2)
(3  --- B_3)

+when I run below group by

select Name from TableA group by Name

-- result will be

Name
A_1
B_3

Looklike it exclude Name=A_2 in my result

+when I applied below group by + count

select Name,count(1) cnt from TableA group by Name

-- result

Name --- cnt
A1   --- 1
**A2   --- 1**
B3   --- 1

It show all rows as expected

I can use distinct to have a correct result for case 1 but still have concern why mysql have that behavior?

Thanks,


texts: Datetime to formatted Date Implicit Conversion

texts:
I am looking at implicit conversions in my stored procedures and the biggest one I have in my prod database is a warning by the Cardinality Estimator in SQL 2014 for converting datetime to formatted date.

I have read the following questions:
Warning in query plan "Cardinality Estimate" and
DateTime to Format: Implicit Conversion but these fail to mention that FORMAT() removes the warning they are only describing that the problem won't be fixed, which is not a solution I looking for, FORMAT() is a viable solution.

Previously we used the formatting option on CONVERT() to format the date, but ANYTIME I use CONVERT() I get an implicit conversion.

CREATE TABLE #temptable ( [Birthdate] datetime )
INSERT INTO #temptable
VALUES
( N'1891-12-30T00:00:00' ),
( N'1918-10-31T00:00:00' ),
( N'1917-12-21T00:00:00' ),
( N'1906-10-29T00:00:00' ),
( N'1916-12-12T00:00:00' ),
( N'1916-11-08T00:00:00' ),
( N'1918-10-01T00:00:00' ),
( N'1913-03-18T00:00:00' ),
( N'1920-08-03T00:00:00' ),
( N'1919-10-22T00:00:00' )

SELECT CONVERT(VARCHAR(26),t.Birthdate,109) AS varConvert,
        CONVERT(NVARCHAR(26), t.Birthdate, 109) AS nvarConvert,
        TRY_CONVERT(VARCHAR(26),t.Birthdate, 109) AS tryCon,
        FORMAT(t.Birthdate, 'MMM dd yyyy') AS form
FROM #temptable AS t

DROP TABLE #temptable

The only not implicit conversion to a formatted date is the FORMAT() function:

Is there a way to not use the FORMAT() function and continue using the Convert function to return formatted dates? Ie do i need to extend or reduce the length of my formatted dates during conversion. I want to avoid implicit conversions, but re-writting all CONVERT() to FORMAT() will take more testing than changing the current CONVERT functionality.


texts: How can I find out that a publication has "replicated schema changes" enabled?

texts:
Where can I find out (preferably using T-SQL) within a published databases, if it would replicate the schema changes?

here is how to create a publication

This topic describes how to create a publication in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

Replicate Schema Changes


texts: The PostgreSQL CREATE DOMAIN command appears to create the domain at the schema level. Is there a best approach I should be aware of that would allow me to  reuse domains across the multiple schemas that comprise my database?


texts:
When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting:

/*
   This will recommend a MAXDOP setting appropriate for your machine's NUMA memory
   configuration.  You will need to evaluate this setting in a non-production
   environment before moving it to production.

   MAXDOP can be configured using:
   EXEC sp_configure 'max degree of parallelism',X;
   RECONFIGURE

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1
   (URL wrapped for readability)
   http://blogs.msdn.com/b/rcormier/archive/2012/10/25/
   you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx

   Biztalk (all versions, including 2010):
   MAXDOP = 1 is only required on the BizTalk Message Box
   database server(s), and must not be changed; all other servers hosting other
   BizTalk Server databases may return this value to 0 if set.
   http://support.microsoft.com/kb/899000
*/

DECLARE @CoreCount int;
DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1
    FROM sys.dm_os_memory_clerks c
    WHERE memory_node_id < 64
    );

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
BEGIN
    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75;

    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    */
    IF @MaxDOP > (@CoreCount / @NumaNodes)
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

    /*
        Reduce @MaxDOP to an even number
    */
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
END
ELSE
BEGIN
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';
END

I realize this is a bit subjective, and can vary based on many things; however I'm attempting to create a tight catch-all piece of code to use as a starting point for a new server.

Does anyone have any input on this code?


texts:
I'm running a simple procedure that reads from a VARCHAR column and inserts data into a TIME column, using this format:

STR_TO_DATE(vTestTime, '%H:%i').

I'm getting the error

  SQL Error (1411): Incorrect datetime value: '09:22' for function str_to_date

I find that a similar statement, SELECT STR_TO_DATE("09:22", "%H:%i") gives me a NULL when I run it from a HeidiSQL client.

I'm sure this syntax used to work and I'm not aware of having changed any settings, can you suggest what I've done wrong?

Edit: Just remembered I have made a recent change, I installed a Wordpress database on the same server, might that have had some affect?

Here's what get running on the server:

mysql> select str_to_date("09:22", "%H:%i");

+-------------------------------+

| str_to_date("09:22", "%H:%i") |

+-------------------------------+

| NULL                          |

+-------------------------------+

1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '09:22' for function str_to_date


texts: Suppressing the removal of leading zeros in numeric data

texts:
I initially declared the data type of the columns for some of the tables I am working on as NUMERIC. Because of that, leading zeros are being removed.  Is there anyway to suppress this behavior?  I need to preserve the leading zeros, since 08 should be considered different from 0000008.

One solution is to changing the definition of the table and changing the numeric to varchar datatype.

Is there any other way to achieve this without changing the table structure?


texts: Why does adding a computed column prevent predicate pushdown?

texts:
I have a weird situation that I don't quite understand.

I have a table like this kinda:

CREATE TABLE dbo.cc_demo
     (
         id INT IDENTITY PRIMARY KEY,
         up_action INT,
         down_action INT,
         last_action_date DATETIME
     );

INSERT dbo.cc_demo ( up_action, down_action, last_action_date )
SELECT TOP 5000000
       nums.num % 500000, nums.num % 500000, DATEADD(MINUTE, nums.num, GETDATE())
FROM
       (   SELECT     ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS num
           FROM       master..spt_values AS sv
           CROSS JOIN master..spt_values AS sv2 ) AS nums;

If I run this query, the plan is just a regular clustered index scan.

SELECT *
FROM   dbo.cc_demo AS cd
WHERE  cd.last_action_date >= '20270601'
AND    cd.last_action_date < '20270901';

But if I add a computed column and index it, my plan changes for the worst.

ALTER TABLE dbo.cc_demo
    ADD total_actions AS up_action + down_action;

CREATE INDEX ix_total_actions ON dbo.cc_demo (total_actions);

Now it scans the clustered index and then filters stuff out way later on!

It asks for an index, and that changes the plan back to what I'd expect, but why do I have to add this?

CREATE NONCLUSTERED INDEX [WORLDSTAR]
    ON dbo.cc_demo ( last_action_date )
    INCLUDE ( up_action, down_action, total_actions );

That doesn't seem logical.

Performance:

Pre-computed column:

Table 'cc_demo'. Scan count 1, logical reads 17990,

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 224 ms.

Post computed column + index:

Table 'cc_demo'. Scan count 1, logical reads 17990

 SQL Server Execution Times:
   CPU time = 594 ms,  elapsed time = 591 ms.

Here's the breakdown:

When I add the computed column, the plan doesn't change
When I index the computed column, it does
I'm not too worried about the performance yet
I'm just curious why the plan changes this way after I index the computed column


texts:
This is a distilled version of the query that I am trying to run:

SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2
ON t1.id = t2.id;

The above query is incredibly slow because MYSQL fails to notice that the subquery in the join i.e. SELECT * FROM table2 WHERE foo = 'bar' has id as a primary key which it can use in the join clause.

Ofcourse, I should have written this query as:

SELECT * FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id
WHERE t2.foo = 'bar';

But, the problem is that the subquery(SELECT * FROM table2 WHERE foo = 'bar') is generated by a library that I do not control. What is the best way for me to optimize this given that I cannot do much about the subquery since I programmatically get it from somewhere else.


texts: MySQL : "FLUSH TABLES WITH READ LOCK" started automatically

texts:
I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being locked up by FLUSH TABLES WITH READ LOCK

  03:21:31  select type_id, count(*) from guid_target_infos group by type_id
  02:38:11  select type_id, count(*) from guid_infos group by type_id
  02:24:29  FLUSH TABLES WITH READ LOCK

But i did not start this command. can someone tell me why it was started automatically?


texts: Why am I getting this counter-intuitive relevancy result to a MySQL full-text query?

texts:
I'm querying for +2019* +sponsor* +BGHDA*

and getting this result:

2019 sponsor BGHDA   #7   Springfield, WA 2019 03 04

relevancy: 34.139

2019 sponsor BGHDA

relevancy: 26.087

Given that my search terms account for a higher percentage of the 2nd result than of the 1st result, I'd expect its relevancy to be higher, but in fact it's lower.

If I remove the + and * from the query leaving 2019 sponsor BGHDA I get similar results, which seems even weirder - the 2nd result is a perfect match!

If I add quotes ("2019 sponsor BGHDA") this problem STILL exhibits itself...

Can someone explain to me in simple terms why this is?  And what tricks I might employ to turn this the other way around?

Thanks


texts:
I am trying to create a function that calculates the moving average for an item. What I have is a table of "item" and then each user can "vote" which produces one vote, with a score, user_id and item_id.

I want to calculate an AVG, which is stored in item_id after every insert and is calculated based on number of vote's (selected by vote.item_id) and averages the score using vote.score.

this is the idea of the query.. this works, but stores the moving average int he last column of vote and is not usable, but I am close!!

select vote.score as single_vote_score, vote.user_id, vote.item_id, items.score as item_score,
    AVG(vote.score)
        OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM vote
    LEFT JOIN items on vote.item_id = items.id
WHERE vote.item_id = 10;

This is the function

CREATE FUNCTION calculate_item_score() RETURNS TRIGGER AS $calculates_items_score_by_average$
    BEGIN UPDATE "item"
        SET "score" = AVG("vote"."score")
        FROM "vote"
        WHERE "item"."id"="vote"."item_id";
        RETURN NEW;
    END;
$calculates_items_score_by_average$
LANGUAGE plpgsql;

This trigger should run every time a vote is inserted!

CREATE TRIGGER "update_item_score"
AFTER INSERT ON "vote"
FOR EACH ROW EXECUTE
PROCEDURE calculate_item_score();

-- this errors out
INSERT INTO vote (user_id, photo_id, score) VALUES ('129','10','50.0');

This is my error

ERROR:  aggregate functions are not allowed in UPDATE
LINE 1: UPDATE "photo" SET "score" = AVG("vote"."score") FROM "vote"...
                                 ^
QUERY:  UPDATE "item" SET "score" = AVG("vote"."score") FROM "vote" WHERE
"item"."id"="vote"."item_id"
CONTEXT:  PL/pgSQL function calculate_item_score() line 1 at SQL statement


texts: postgres: "alter default privileges" issue

texts:
I am implementing a schema access model on postgres (I know oracle). Using guidance from http://bonesmoses.org/2016/02/26/pg-phriday-corralling-the-hordes/ I created a data owner account and two roles (_ro and _rw), and a second user account which is granted the _rw role.
I created a table in the Owner account which I expect to be automatically accessible via the roles because I issued a "ALTER DEFAULT PRIVILEGES" command to automatically issue GRANTs in future-created tables. However, when I log in as the user account, a select on the table returns "ERROR:  permission denied for relation checklist1new"

Create the Owner account

CREATE   USER access1 WITH PASSWORD 'whatever';
CREATE SCHEMA access1 AUTHORIZATION access1;
ALTER USER access1 SET search_path = access1;
ALTER SCHEMA access1 OWNER TO access1;

CREATE GROUP access1_ro;
CREATE GROUP access1_rw;

GRANT USAGE ON SCHEMA access1 TO access1_ro;
GRANT USAGE ON SCHEMA access1 TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT SELECT ON TABLES TO access1_ro;

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1
GRANT USAGE ON SEQUENCES TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

CREATE TABLE TEST_TABLE_access1(col1 integer);

Create the user account

CREATE USER user1 WITH PASSWORD 'whatever';
GRANT CONNECT on DATABASE rwinkwavu to user1;
GRANT access1_rw to user1;

Attempt to query the table, logged in as user1

select * from access1.TEST_TABLE_access1
ERROR:  permission denied for relation TEST_TABLE_access1

I expected this query to return 0 rows. What am I doing wrong?
Postgres 9.4 on macOS.


texts: combining two different record into single result

texts:
I am fetching data from 3 tables with the help of join. My output is in JSON format.

Output in JSON format:-

{"order_id":"2",
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

{"order_id":"2",
"location_address":
"Testing location Delhi",
"emp_first_name":"**abhijeet**",
"order_status":"0",
"from_date":"0000-00-00 00:00:00",
"actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

The problem is, In above output, All the data is same but emp_first_name are different because both are having seperate id's. Now i want to combine the data of both the emp_first_name and display it as single output like

The output which i want :-

{"order_id":"2",
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet,abhijeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

QUery I Used :-

$this->datatables
   ->select('o.order_id,
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address,
        e.emp_first_name, o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id');

Any help would be appreciated. Thank you in advance.