Dev Shed Forums - MySQL Help http://forums.devshed.com/ MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS). en Mon, 20 Nov 2017 07:12:01 GMT vBulletin 60 http://forums.devshed.com/images/misc/rss.png Dev Shed Forums - MySQL Help http://forums.devshed.com/ Concat Help http://forums.devshed.com/mysql-help/979474-concat-help-new-post.html Fri, 17 Nov 2017 00:24:51 GMT Hello I am using an application which can use mysql to manipulate a csv file - please note this may be not the orthodox mysql methods and ways which are applied however uses same logic. I have a 8 columns in the csv files with images url for products which i am trying to Concat and use a character ; to separate them i am also using a Replace to change the url names in the csv columns... Hello

I am using an application which can use mysql to manipulate a csv file - please note this may be not the orthodox mysql methods and ways which are applied however uses same logic.
I have a 8 columns in the csv files with images url for products

which i am trying to Concat and use a character ; to separate them

i am also using a Replace to change the url names in the csv columns to different url


This is what i have now

REPLACE(
CONCAT (

[CSV_COL(24)] ';'
[CSV_COL(25)], ';'
[CSV_COL(26)], ';'
[CSV_COL(27)], ';'
[CSV_COL(28)], ';'
[CSV_COL(29)], ';'
[CSV_COL(30)], ';'
[CSV_COL(31)]),


'https://oldurl.com/washing_machine','http://www.newurl.com/washing_machine'

)

This works however some products have 1 images some have 2 or 3 or 4 or 5 or 6 - or 7 or 8
and the cells in the csv files for these products are blank
this gives me output results like this

;;';;;';


'http://www.newurl.com/washing_machine;;';;;';'

'http://www.newurl.com/washing_machine;[url]http://www.newurl.com/cooker;;';;;';'

at the end of rows for products with less than the 8 images
I am trying trim however i am struggling with the syntax as i mentioned the application runs on what is says is MYSQL expressions and is based on same MYSQL logic so i have to do trial and error

Can anyone suggest a way of removing ;;';;;';

Or is there an alternative way to concat to say

If [CSV_COL(24)] is not empty then Concat with [CSV_COL(25)]
Thank you for taking the time to read this spent 2 days on this but no joy ]]>
MySQL Help nrt453 http://forums.devshed.com/mysql-help-4/concat-help-979474.html
Lock tables and/or transaction for stock table? http://forums.devshed.com/mysql-help/979435-lock-tables-transaction-stock-table-new-post.html Mon, 13 Nov 2017 03:45:21 GMT How can I maintain data integrity for a stock table with InnoDB? Does this require the use of both a table lock and a transaction? I have tried to find some good examples, but even well known opensource ecommerce software doesn't implement table locks/transactions. Would below code work? Do I really need to lock the whole table, or is there a way to only lock the rows of the sold products? Or does anyone have a good example?

Code:

CREATE TABLE `stock` (
`product_id` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`quantity` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

Code:

LOCK TABLES stock WRITE;
START TRANSACTION;
SET autocommit = 0;
UPDATE stock SET quantity = quantity - 4 WHERE product_id = 'PRODUCT_1' AND quantity >= 4;
UPDATE stock SET quantity = quantity - 2 WHERE product_id = 'PRODUCT_2' AND quantity >= 2;
UPDATE stock SET quantity = quantity - 5 WHERE product_id = 'PRODUCT_3' AND quantity >= 5;
COMMIT or ROLLBACK;

COMMIT if all UPDATE queries have 1 affected row, otherwise ROLLBACK ]]>
MySQL Help anonty http://forums.devshed.com/mysql-help-4/lock-tables-transaction-stock-table-979435.html
MySQL Server Connection Lost http://forums.devshed.com/mysql-help/979434-mysql-server-connection-lost-new-post.html Mon, 13 Nov 2017 02:39:05 GMT Hi, I have run into an issue with my ecommerce website database. A restore had to be performed some time ago and it is my belief that I have been experiencing this ever since. It has grown my error log to nearly 2GB. This is the email that I started to get recently and I do not know what may have changed to cause an issue. I would appreciate any help in direction as to how I may be able... Hi, I have run into an issue with my ecommerce website database. A restore had to be performed some time ago and it is my belief that I have been experiencing this ever since. It has grown my error log to nearly 2GB.

This is the email that I started to get recently and I do not know what may have changed to cause an issue.

I would appreciate any help in direction as to how I may be able to resolve the issue. It appears that the DB or cache files may be corrupt. I have run CHECK TABLE for all of the mysql and website databases to no avail. I tried to check the MyISAM tables as well:
myisamchk --silent --force */*.MYI
myisamchk: error: 140 when opening MyISAM-table...

"Cpanel::Exception::Database::Error/(XID ss6qy5) The system received an error from the MySQL database mysql: 2013 (Lost connection to MySQL server during query) at /usr/local/cpanel/Cpanel/Exception/CORE.pm line 336.
Cpanel::Exception::create("Database::Error", ARRAY(0x30e5208)) called at /usr/local/cpanel/Cpanel/Exception.pm line 46
Cpanel::Exception::__ANON__(__CPANEL_HIDDEN__, ARRAY(0x30e5208)) called at /usr/local/cpanel/Cpanel/DBI.pm line 200
Cpanel::DBI::_create_exception(Cpanel::DBI::Mysql::st=HASH(0x30d3aa0), "DBD::mysql::st execute failed: Lost connection to MySQL serve"..., undef) called at /usr/local/cpanel/Cpanel/DBI.pm line 188
Cpanel::DBI::_error_handler("DBD::mysql::st execute failed: Lost connection to MySQL serve"..., Cpanel::DBI::Mysql::st=HASH(0x30d3aa0), undef) called at bin/update_db_cache.pl line 333
Script::Update::DB::Cache::can_get_mysql_usage(HASH(0xff6ea0), HASH(0x10651b0), Cpanel::DB::Map::Collection::Index=HASH(0x3098128)) called at bin/update_db_cache.pl line 60
Script::Update::DB::Cache::script("Script::Update::DB::Cache") called at bin/update_db_cache.pl line 35"

--------------------------

More info:
It tends to crash ever 2-4 hours and no changes out of the ordinary are being made to my knowledge.

# Environment
CentOS, WHM/CPanel, PHP7, MySQL 5.6.38, eCommerce Website (Magento)

# Below from website.err file:
...
InnoDB: End of page dump
2017-11-12 08:31:38 7fece4bb5700 InnoDB: uncompressed page, stored checksum in field1 1940515531, calculated checksums for field1: crc32 1062689690, innodb 4217881636, none 3735928559, stored checksum in field2 0, calculated checksums for field2: crc32 1062689690, innodb 145366930, none 3735928559, page LSN 6 2465970045, low 4 bytes of LSN at page end 0, page number (if stored to page already) 1, space id (if created with >= MySQL-4.1.1 and stored already) 84948
InnoDB: Page may be an insert buffer bitmap page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also 301 Moved Permanently
InnoDB: about forcing recovery.
InnoDB: Error: Unable to read tablespace 84948 page no 1 into the buffer pool after 100 attempts
InnoDB: The most probable cause of this error may be that the table has been corrupted.
InnoDB: You can try to fix this problem by using innodb_force_recovery.
InnoDB: Please see reference manual for more details.
InnoDB: Aborting...
2017-11-12 08:31:38 7fece4bb5700 InnoDB: Assertion failure in thread 140655426492160 in file buf0buf.cc line 2740
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to 301 Moved Permanently.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: 301 Moved Permanently
InnoDB: about forcing recovery.
13:31:38 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=5
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68108 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x3885780
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fece4bb4e48 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d7265]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x664b84]
/lib64/libpthread.so.0(+0xf7e0)[0x7fecf5e587e0]
/lib64/libc.so.6(gsignal+0x35)[0x7fecf4b03495]
/lib64/libc.so.6(abort+0x175)[0x7fecf4b04c75]
/usr/sbin/mysqld[0xa84b0d]
/usr/sbin/mysqld[0x9970fe]
/usr/sbin/mysqld[0x99d4f0]
/usr/sbin/mysqld[0xa8094f]
/usr/sbin/mysqld[0xa96124]
/usr/sbin/mysqld[0xa9656b]
/usr/sbin/mysqld[0xa84771]
/usr/sbin/mysqld[0xa6db06]
/usr/sbin/mysqld[0xa15029]
/usr/sbin/mysqld[0x982985]
/usr/sbin/mysqld[0x983b0b]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x5aa35e]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x684)[0x76cda4]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1c8)[0x69b838]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0xc9f)[0x69e0cf]
/usr/sbin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4f)[0x69e1ff]
/usr/sbin/mysqld[0x71892c]
/usr/sbin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x6c0)[0x71f580]
/usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x2f1)[0x70c8f1]
/usr/sbin/mysqld(_ZN4JOIN14prepare_resultEPP4ListI4ItemE+0x9d)[0x700d8d]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x142)[0x6bf192]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_ resultP18st_select_lex_unitP13st_select_lex+0x250)[0x702810]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x19f)[0x7030af]
/usr/sbin/mysqld[0x6de3fd]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1952)[0x6e11e2]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x378)[0x6e4628]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x111e)[0x6e57ee]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b1ddf]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b1f07]
/lib64/libpthread.so.0(+0x7aa1)[0x7fecf5e50aa1]
/lib64/libc.so.6(clone+0x6d)[0x7fecf4bb9bcd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fec98004bb0): is an invalid pointer
Connection ID (thread ID): 29
Status: NOT_KILLED

The manual page at 301 Moved Permanently contains
information that should help you find out what is causing the crash.

------------end--------------

Thank you ahead! ]]>
MySQL Help beav http://forums.devshed.com/mysql-help-4/mysql-server-connection-lost-979434.html
multi level joint query http://forums.devshed.com/mysql-help/979430-multi-level-joint-query-new-post.html Sun, 12 Nov 2017 14:20:41 GMT Dear All I am sorry my english is no good, i have already tried to search about this question but still no good result. I have 3 tables and want to mak a view ( query with join ). transakTB : transak_id, suplier_id, suplierTB: suplier_id, supplier_Name, product_id Dear All

I am sorry my english is no good, i have already tried to search about this question but still no good result.

I have 3 tables and want to mak a view ( query with join ).
transakTB :
transak_id, suplier_id,

suplierTB:
suplier_id, supplier_Name, product_id

productTB :
product_id, product_name

i want to make a joint query which is consist of :
transak_id, suplier_name, product_name

how can i do it with join query?

Thanks for any kind help
Best Regards
Mchoud ]]>
MySQL Help mchoud http://forums.devshed.com/mysql-help-4/multi-level-joint-query-979430.html
Wondering about cross Joins http://forums.devshed.com/mysql-help/979378-wondering-cross-joins-new-post.html Fri, 03 Nov 2017 17:04:43 GMT I am trying to do something I am not sure if it is possible. I want to add a couple of queries together. But there wont be results in all columns. E.name s.date v.date There is only one s.date for each name, there are many v.date for each name. So I am getting undesirable cross joins or strange results. I am trying to do something I am not sure if it is possible.

I want to add a couple of queries together. But there wont be results in all columns.

E.name s.date v.date

There is only one s.date for each name, there are many v.date for each name.

So I am getting undesirable cross joins or strange results.

My query that I am messing with is like so:

Code:

SELECT e.name, CONCAT(s._date,s.am_pm)
FROM employee as e
JOIN stat_picks as s
ON e.employee_id = s.employee_id
JOIN vacation_picks as v
ON v.employee_id = e.employee_id
WHERE e.platoon_id = 1 and e.retired IS NULL

I am not sure what kind of joins to use. Or if this is even possible?

Thanks. ]]>
MySQL Help SGC3 http://forums.devshed.com/mysql-help-4/wondering-cross-joins-979378.html
Synch 2 databases http://forums.devshed.com/mysql-help/979358-synch-2-databases-new-post.html Wed, 01 Nov 2017 10:35:46 GMT Hi; I have 2 databases, 1 app and 1 dev. App has client data as in dev has dummy data but newer structure. How can I copy the structure of dev to app db without losing any app data? Thanks Hi;

I have 2 databases, 1 app and 1 dev.

App has client data as in dev has dummy data but newer structure.

How can I copy the structure of dev to app db without losing any app data?

Thanks ]]>
MySQL Help English Breakfast Tea http://forums.devshed.com/mysql-help-4/synch-2-databases-979358.html
What happens if in the middle of a batch update laptop runs out of battery? http://forums.devshed.com/mysql-help/979339-happens-middle-batch-update-laptop-runs-battery-new-post.html Sat, 28 Oct 2017 07:08:15 GMT What happens if in the middle of a batch update laptop runs out of battery? What happens if in the middle of a batch update laptop runs out of battery? ]]> MySQL Help English Breakfast Tea http://forums.devshed.com/mysql-help-4/happens-middle-batch-update-laptop-runs-battery-979339.html SQL: In this table but not that table http://forums.devshed.com/mysql-help/979318-sql-table-table-new-post.html Wed, 25 Oct 2017 07:07:03 GMT The objective is to find Puid's that are in table a but not in table b.


SELECT a.Puid FROM a LEFT OUTER JOIN b
ON (a.Puid = b.Puid) WHERE b.Puid IS NULL


Will this do it? ]]>
MySQL Help Arty Zifferelli http://forums.devshed.com/mysql-help-4/sql-table-table-979318.html
Is it correct to use SUM and GROUP BY in the same query? http://forums.devshed.com/mysql-help/979304-correct-sum-query-new-post.html Mon, 23 Oct 2017 09:00:46 GMT Is it approved by SimplySql people? Seems right when I look at the answers. Code: --------- SELECT Sum(pks_amount) AS total, Count(*) AS quantity FROM transactions Is it approved by SimplySql people?

Seems right when I look at the answers.

Code:

SELECT Sum(pks_amount) AS total,
      Count(*)        AS quantity
FROM  transactions
WHERE  pks_product_id = 6212
      AND pks_mode = 'live'
      AND pks_event = 'sales'

Code:

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_time` varchar(255) NOT NULL,
  `receipt` varchar(255) NOT NULL,
  `transaction_type` varchar(255) NOT NULL,
  `affiliate` varchar(255) NOT NULL,
  `payment_method` varchar(255) NOT NULL,
  `item_no` int(11) NOT NULL,
  `recurring` varchar(255) NOT NULL,
  `line_item_type` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `stripe_price` decimal(6,2) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `upsell_original_receipt` varchar(255) NOT NULL,
  `upsell_flow_id` int(11) unsigned zerofill NOT NULL,
  `username` varchar(255) NOT NULL,
  `notes` text NOT NULL,
  `ontraport_user_id` int(11) DEFAULT NULL,
  `payment_system` varchar(255) DEFAULT NULL,
  `stripe_order_id` varchar(255) DEFAULT NULL,
  `stripe_description` text,
  `ontraport_id_from_srtipe` int(11) DEFAULT NULL,
  `stripe_meta_data_contact_id` int(11) DEFAULT NULL,
  `status_in_our_db` varchar(255) DEFAULT 'active',
  `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,
  `jvzoo_cproditem` varchar(255) DEFAULT NULL,
  `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,
  `jvzoo_ctransamount` int(11) DEFAULT NULL,
  `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,
  `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,
  `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,
  `jvzoo_caffitid` varchar(255) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `stats_in_our_database` varchar(255) DEFAULT 'inactive',
  `op_invoice_id` int(11) DEFAULT NULL,
  `clickbank_transaction` int(11) DEFAULT NULL,
  `jvzoo_transaction` int(11) DEFAULT NULL,
  `pks_transaction` int(11) DEFAULT NULL,
  `pks_amount` decimal(6,2) DEFAULT NULL,
  `pks_item_number` int(11) DEFAULT NULL,
  `pks_mode` varchar(255) DEFAULT NULL,
  `pks_payment_processor` varchar(255) DEFAULT NULL,
  `pks_buyer_ip` varchar(255) DEFAULT NULL,
  `pks_event` varchar(255) DEFAULT NULL,
  `pks_transaction_id` varchar(255) DEFAULT NULL,
  `pks_tracking_id` varchar(255) DEFAULT NULL,
  `pks_product_id` int(11) DEFAULT NULL,
  `pks_product_name` varchar(255) DEFAULT NULL,
  `pks_buyer_email` varchar(255) DEFAULT NULL,
  `pks_transaction_time` varchar(255) DEFAULT NULL,
  `pks_invoice_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3582 DEFAULT CHARSET=utf8 COMMENT='ALTER TABLE employees\n  ADD last_name VARCHAR(50);\n\n`id` int(11) NOT NULL AUTO_INCREMENT,\n  `transaction_time` varchar(255) NOT NULL,\n  `receipt` varchar(255) NOT NULL,\n  `transaction_type` varchar(255) NOT NULL,\n  `affiliate` varchar(255) NOT NULL,\n  `payment_method` varchar(255) NOT NULL,\n  `item_no` int(11) NOT NULL,\n  `recurring` varchar(255) NOT NULL,\n  `line_item_type` varchar(255) NOT NULL,\n  `first_name` varchar(255) NOT NULL,\n  `stripe_price` decimal(4,2) DEFAULT NULL,\n  `email` varchar(255) NOT NULL,\n  `upsell_original_receipt` varchar(255) NOT NULL,\n  `upsell_flow_id` int(11) unsigned zerofill NOT NULL,\n  `username` varchar(255) NOT NULL,\n  `notes` text NOT NULL,\n  `ontraport_user_id` int(11) DEFAULT NULL,\n  `payment_system` varchar(255) DEFAULT NULL,\n  `stripe_order_id` varchar(255) DEFAULT NULL,\n  `stripe_description` text,\n  `ontraport_id_from_srtipe` int(11) DEFAULT NULL,\n  `stripe_meta_data_contact_id` int(11) DEFAULT NULL,\n  `status_in_our_db` varchar(255) DEFAULT ''active'',\n  `jvzoo_cprodtitle` varchar(255) DEFAULT NULL,\n  `jvzoo_cproditem` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransaffiliate` varchar(255) DEFAULT NULL,\n  `jvzoo_ctransamount` int(11) DEFAULT NULL,\n  `jvzoo_ctranspaymentmethod` varchar(4) DEFAULT NULL,\n  `jvzoo_ctransvendor` varchar(255) DEFAULT NULL,\n  `jvzoo_cupsellreceipt` varchar(255) DEFAULT NULL,\n  `jvzoo_caffitid` varchar(255) DEFAULT NULL,';

]]>
MySQL Help English Breakfast Tea http://forums.devshed.com/mysql-help-4/correct-sum-query-979304.html